﻿using System;
using System.Collections.Generic;
using System.Text;
using RaisingStudio.Data.Common.Managers;
using System.Reflection;
using RaisingStudio.Common.Reflection;
using RaisingStudio.Data.Common.Expressions;
using RaisingStudio.Common;

namespace RaisingStudio.Data.Common.Builders
{
    /// <summary>
    /// Common Command builder.
    /// </summary>
    public class CommonCommandBuilder
    {
        #region static member
        /// <summary>
        /// Condition Command spliter string.
        /// </summary>
        public const string CONDITIONCOMMANDAPPENDTEXT = " ";

        public const string COMMONCOMMAND_PARAMETER_PREFIX = "@";

        /// <summary>
        /// Format string of parameter name.
        /// </summary>
        public const string PARAMETERNAMEFORMAT = "@p{0}";

        /// <summary>
        /// Check an array of string contains the specified string or not.
        /// </summary>
        /// <param name="stringArray">An array of string.</param>
        /// <param name="value">The specified string.</param>
        /// <returns>If contains return true, otherwise return false.</returns>
        public static bool ContainsValue(string[] stringArray, string value)
        {
            return (System.Array.IndexOf(stringArray, value) >= 0);
        }

        /// <summary>
        /// Remove characters.
        /// </summary>
        /// <param name="stringBuilder">string builder.</param>
        /// <param name="length">count of characters.</param>
        /// <returns>after removed.</returns>
        public static System.Text.StringBuilder RemoveEnd(ref System.Text.StringBuilder stringBuilder, int length)
        {
            return stringBuilder.Remove(stringBuilder.Length - length, length);
        }
        
        /// <summary>
        /// Create FROM sub command.
        /// </summary>
        /// <param name="xmlElement">xml element.</param>
        /// <param name="dataTableMapping">mapping.</param>
        /// <returns>FROM sub command.</returns>
        private static string BuildFromCommandText(System.Xml.XmlElement xmlElement, out System.Data.Common.DataTableMapping dataTableMapping)
        {
            string fromCommandText;

            string dataTableTableName = xmlElement.Attributes["TableName"].Value.Trim();
            string dataTableSourceName = dataTableTableName;
            if (xmlElement.Attributes["SourceName"] != null)
            {
                dataTableSourceName = xmlElement.Attributes["SourceName"].Value.Trim();
            }
            string dataTableSourceAlias = dataTableSourceName;
            if (xmlElement.Attributes["SourceAlias"] != null)
            {
                dataTableSourceAlias = xmlElement.Attributes["SourceAlias"].Value.Trim();
            }

            if (dataTableSourceName == dataTableSourceAlias)
            {
                fromCommandText = string.Format(" FROM {0}", dataTableSourceName);
                dataTableMapping = new System.Data.Common.DataTableMapping(dataTableSourceName, dataTableTableName);
            }
            else
            {
                fromCommandText = string.Format(" FROM {0} {1}", dataTableSourceName, dataTableSourceAlias);
                dataTableMapping = new System.Data.Common.DataTableMapping(dataTableSourceAlias, dataTableTableName);
            }

            return fromCommandText;
        }

        private string BuildFromCommandText(DataTableDefinition dataTableDefinition, out System.Data.Common.DataTableMapping dataTableMapping)
        {
            string fromCommandText;

            string dataTableTableName = dataTableDefinition.TableName;
            string dataTableSourceName = dataTableTableName;
            if (!string.IsNullOrEmpty(dataTableDefinition.SourceName))
            {
                dataTableSourceName = dataTableDefinition.SourceName;
            }
            fromCommandText = string.Format(" FROM {0}", dataTableSourceName);
            dataTableMapping = new System.Data.Common.DataTableMapping(dataTableSourceName, dataTableTableName);

            return fromCommandText;
        }

        /// <summary>
        /// append column sub command.
        /// </summary>
        /// <param name="dataColumnColumnName">Column name.</param>
        /// <param name="childXmlElement">xml element.</param>
        /// <param name="selectCommandTextStringBuilder">string builder.</param>
        /// <param name="dataTableMapping">mapping.</param>
        private static void AppendColumnCommandText(string dataColumnColumnName, System.Xml.XmlElement childXmlElement, ref System.Text.StringBuilder selectCommandTextStringBuilder, ref System.Data.Common.DataTableMapping dataTableMapping)
        {
            string dataColumnSourceName = dataColumnColumnName;
            if (childXmlElement.Attributes["SourceName"] != null)
            {
                dataColumnSourceName = childXmlElement.Attributes["SourceName"].Value.Trim();
            }
            string dataColumnSourceAlias = dataColumnSourceName;
            if (childXmlElement.Attributes["SourceAlias"] != null)
            {
                dataColumnSourceAlias = childXmlElement.Attributes["SourceAlias"].Value.Trim();
            }

            if (dataColumnSourceName == dataColumnSourceAlias)
            {
                selectCommandTextStringBuilder.Append(dataColumnSourceName);
                dataTableMapping.ColumnMappings.Add(dataColumnSourceName, dataColumnColumnName);
            }
            else
            {
#if (PocketPC || Smartphone || WindowsCE)
                selectCommandTextStringBuilder.Append(string.Format("{0} AS {1}", dataColumnSourceName, dataColumnSourceAlias));
#else
                selectCommandTextStringBuilder.AppendFormat("{0} AS {1}", dataColumnSourceName, dataColumnSourceAlias);
#endif
                dataTableMapping.ColumnMappings.Add(dataColumnSourceAlias, dataColumnColumnName);
            }
        }

        private void AppendColumnCommandText(string dataColumnColumnName, DataColumnDefinition dataColumnDefinition, ref StringBuilder selectCommandTextStringBuilder, ref System.Data.Common.DataTableMapping dataTableMapping)
        {
            string dataColumnSourceName = dataColumnColumnName;
            if (!string.IsNullOrEmpty(dataColumnDefinition.SourceName))
            {
                dataColumnSourceName = dataColumnDefinition.SourceName;
            }
            selectCommandTextStringBuilder.Append(dataColumnSourceName);
            dataTableMapping.ColumnMappings.Add(dataColumnSourceName, dataColumnColumnName);
        }

        /// <summary>
        /// append addtion sub command.
        /// </summary>
        /// <param name="xmlElement">xml element.</param>
        /// <param name="selectCommandTextStringBuilder">string builder.</param>
        private static void AppendAddtionCommandText(System.Xml.XmlElement xmlElement, ref System.Text.StringBuilder selectCommandTextStringBuilder)
        {
            // Add addtion Command.
            if (xmlElement.Attributes["AddtionCommand"] != null)
            {
                selectCommandTextStringBuilder.Append(xmlElement.Attributes["AddtionCommand"].Value);
            }
        }

        private void AppendAddtionCommandText(DataTableDefinition dataTableDefinition, ref StringBuilder selectCommandTextStringBuilder)
        {
        }


        /// <summary>
        /// Gets Wellknown Type.
        /// </summary>
        /// <param name="dataType">data Type name.</param>
        /// <returns>Data type.</returns>
        public static System.Data.DbType GetWellKnownDbType(string dataType)
        {
            return RaisingStudio.Common.Reflection.TypeManager.GetWellKnownDbType(dataType);
        }

        /// <summary>
        /// Generate table structure definition.
        /// </summary>
        /// <param name="xmlElement">xml element.</param>
        /// <returns>table structure definition.</returns>
        public static DataTableDefinition GenerateDataTableDefinition(System.Type type, System.Xml.XmlElement xmlElement)
        {
            DataTableDefinition dataTableDefinition = new DataTableDefinition();
            if (xmlElement != null)
            {
                GenerateDataTableDefinition(type, xmlElement, dataTableDefinition);
            }
            else
            {
                GenerateDataTableDefinition(type, dataTableDefinition);
            }
            return dataTableDefinition;
        }

        private static void GenerateDataTableDefinition(System.Type type, System.Xml.XmlElement xmlElement, DataTableDefinition dataTableDefinition)
        {
            #region include Relation
            if (xmlElement.Attributes["IncludeDefinition"] != null)
            {
                // Has include relation.
                string includeDefinitionName = xmlElement.Attributes["IncludeDefinition"].Value.Trim();

                if (xmlElement.Attributes["IncludeType"] != null)
                {
                    string includeTypeName = xmlElement.Attributes["IncludeType"].Value.Trim();
                    string typeName = includeTypeName;
                    System.Reflection.Assembly assembly;
                    if (typeName.IndexOf(",") >= 0)
                    {
                        string[] names = typeName.Split(',');
                        assembly = System.Reflection.Assembly.LoadFrom(names[0]);
                        typeName = names[1];
                    }
                    else
                    {
                        assembly = type.Assembly;
                    }
                    System.Type includeType = assembly.GetType(typeName);

                    System.Xml.XmlElement includeXmlElement = DefinitionManager.Instance.LoadXmlDocument(includeType, includeDefinitionName);
                    DataTableDefinition includeDefinition = GenerateDataTableDefinition(includeType, includeXmlElement);
                    dataTableDefinition.IncludeDefinition = includeDefinition;
                }
                else
                {
                    System.Xml.XmlElement includeXmlElement = DefinitionManager.Instance.LoadXmlDocument(type, includeDefinitionName);
                    DataTableDefinition includeDefinition = GenerateDataTableDefinition(type, includeXmlElement);
                    dataTableDefinition.IncludeDefinition = includeDefinition;
                }
            }
            #endregion

            string dataTableTableName = null;
            if (xmlElement.Attributes["TableName"] != null)
            {
                dataTableTableName = xmlElement.Attributes["TableName"].Value.Trim();
            }
            else
            {
                if (dataTableDefinition.IncludeDefinition != null)
                {
                    dataTableTableName = dataTableDefinition.IncludeDefinition.TableName;
                }
            }
            dataTableDefinition.TableName = dataTableTableName;

            if (dataTableDefinition.IncludeDefinition != null)
            {
                string dataTableSourceName = null;
                if (xmlElement.Attributes["SourceName"] != null)
                {
                    dataTableSourceName = xmlElement.Attributes["SourceName"].Value.Trim();
                }
                dataTableDefinition.SourceName = dataTableSourceName;
            }
            else
            {
                string dataTableSourceName = dataTableTableName;
                if (xmlElement.Attributes["SourceName"] != null)
                {
                    dataTableSourceName = xmlElement.Attributes["SourceName"].Value.Trim();
                }
                dataTableDefinition.SourceName = dataTableSourceName;
            }

            foreach (System.Xml.XmlNode childXmlNode in xmlElement)
            {
                if (childXmlNode is System.Xml.XmlElement)
                {
                    System.Xml.XmlElement childXmlElement = childXmlNode as System.Xml.XmlElement;
                    if (childXmlElement.Name == "DataColumn")
                    {
                        string dataColumnColumnName = childXmlElement.Attributes["ColumnName"].Value.Trim();
                        string dataColumnSourceName = dataColumnColumnName;
                        if (childXmlElement.Attributes["SourceName"] != null)
                        {
                            dataColumnSourceName = childXmlElement.Attributes["SourceName"].Value.Trim();
                        }

                        DataColumnDefinition dataColumnDefinition = new DataColumnDefinition(dataColumnColumnName, dataColumnSourceName);
                        #region process primary key
                        if (childXmlElement.Attributes["PrimaryKey"] != null)
                        {
                            bool primaryKey = bool.Parse(childXmlElement.Attributes["PrimaryKey"].Value);
                            if (primaryKey)
                            {
                                dataTableDefinition.PrimaryKeys.Add(dataColumnColumnName);
                            }
                        }
                        #endregion
                        if (childXmlElement.Attributes["AllowDBNull"] != null)
                        {
                            dataColumnDefinition.AllowDBNull = bool.Parse(childXmlElement.Attributes["AllowDBNull"].Value);
                        }
                        if (childXmlElement.Attributes["AutoIncrement"] != null)
                        {
                            dataColumnDefinition.AutoIncrement = bool.Parse(childXmlElement.Attributes["AutoIncrement"].Value);
                        }
                        if (childXmlElement.Attributes["DataType"] != null)
                        {
                            dataColumnDefinition.DbType = GetWellKnownDbType(childXmlElement.Attributes["DataType"].Value.Trim());
                        }
                        dataTableDefinition.Columns.Add(dataColumnColumnName, dataColumnDefinition);
                    }
                }
            }
        }

        private static void GenerateDataTableDefinition(System.Type type, DataTableDefinition dataTableDefinition)
        {
            dataTableDefinition.TableName = type.Name;
            DataTableAttribute dataTableAttribute = Attribute.GetCustomAttribute(type, typeof(DataTableAttribute)) as DataTableAttribute;
            if (dataTableAttribute != null)
            {
                if (!string.IsNullOrEmpty(dataTableAttribute.TableName))
                {
                    dataTableDefinition.TableName = dataTableAttribute.TableName;
                }
                if (!string.IsNullOrEmpty(dataTableAttribute.SourceName))
                {
                    dataTableDefinition.SourceName = dataTableAttribute.SourceName;
                }
                else
                {
                    dataTableDefinition.SourceName = dataTableDefinition.TableName;
                }
            }
            else
            {
                dataTableDefinition.SourceName = dataTableDefinition.TableName;
            }

            // Columns
            PropertyInfo[] properties = type.GetProperties();
            if ((properties != null) && (properties.Length > 0))
            {
                foreach (PropertyInfo propertyInfo in properties)
                {
                    DataColumnDefinition dataColumnDefinition = new DataColumnDefinition();
                    dataColumnDefinition.ColumnName = propertyInfo.Name;
                    DataColumnAttribute dataColumnAttribute = Attribute.GetCustomAttribute(propertyInfo, typeof(DataColumnAttribute)) as DataColumnAttribute;
                    if (dataColumnAttribute != null)
                    {
                        if (!string.IsNullOrEmpty(dataColumnAttribute.ColumnName))
                        {
                            dataColumnDefinition.ColumnName = dataColumnAttribute.ColumnName;
                        }
                        if (!string.IsNullOrEmpty(dataColumnAttribute.SourceName))
                        {
                            dataColumnDefinition.SourceName = dataColumnAttribute.SourceName;
                        }
                        else
                        {
                            dataColumnDefinition.SourceName = dataColumnDefinition.ColumnName;
                        }

                        // Other.
                        dataColumnDefinition.AllowDBNull = dataColumnAttribute.AllowDBNull;
                        dataColumnDefinition.AutoIncrement = dataColumnAttribute.AutoIncrement;
                        if (!dataColumnAttribute.IsDbTypeNull)
                        {
                            dataColumnDefinition.DbType = dataColumnAttribute.DbType;
                        }
                        else
                        {
                            Type propertyType = propertyInfo.PropertyType;
                            if (propertyType.IsGenericType)
                            {
                                if (propertyType.GetGenericTypeDefinition() == typeof(Nullable<>))
                                {
                                    Type argumentsType = propertyType.GetGenericArguments()[0];
                                    if (argumentsType.IsEnum)
                                    {
                                        dataColumnDefinition.DbType = System.Data.DbType.String;
                                    }
                                    else
                                    {
                                        dataColumnDefinition.DbType = GetDefaultDbType(argumentsType);
                                    }
                                }
                            }
                            else if (propertyType.IsEnum)
                            {
                                dataColumnDefinition.DbType = System.Data.DbType.Int32;
                            }
                            else
                            {
                                dataColumnDefinition.DbType = GetDefaultDbType(propertyType);
                            }
                        }

                        // PrimaryKey.
                        if (dataColumnAttribute.IsPrimaryKey)
                        {
                            dataTableDefinition.PrimaryKeys.Add(dataColumnDefinition.ColumnName);
                        }
                        dataTableDefinition.Columns.Add(dataColumnDefinition.ColumnName, dataColumnDefinition);
                    }
                }
            }
        }

        private static System.Data.DbType GetDefaultDbType(Type type)
        {
            return TypeManager.GetWellKnownDbType(TypeManager.GetWellKnownDataTypeName(type));
        }

        /// <summary>
        /// Generate table structure definition.
        /// </summary>
        /// <param name="xmlElement">xml element.</param>
        /// <returns>table structure definition.</returns>
        public static DataTableDefinition GenerateDataViewDefinition(System.Type type, System.Xml.XmlElement xmlElement)
        {
            DataTableDefinition dataTableDefinition = new DataTableDefinition();

            #region include Relation 
            if (xmlElement.Attributes["IncludeDefinition"] != null)
            {
                // Has include relation.
                string includeDefinitionName = xmlElement.Attributes["IncludeDefinition"].Value.Trim();

                if (xmlElement.Attributes["IncludeType"] != null)
                {
                    string includeTypeName = xmlElement.Attributes["IncludeType"].Value.Trim();
                    string typeName = includeTypeName;
                    System.Reflection.Assembly assembly;
                    if (typeName.IndexOf(",") >= 0)
                    {
                        string[] names = typeName.Split(',');
                        assembly = System.Reflection.Assembly.LoadFrom(names[0]);
                        typeName = names[1];
                    }
                    else
                    {
                        assembly = type.Assembly;
                    }
                    System.Type includeType = assembly.GetType(typeName);

                    System.Xml.XmlElement includeXmlElement = DefinitionManager.Instance.LoadXmlDocument(includeType, includeDefinitionName);
                    DataTableDefinition includeDefinition = GenerateDataTableDefinition(includeType, includeXmlElement);
                    dataTableDefinition.IncludeDefinition = includeDefinition;
                }
                else
                {
                    System.Xml.XmlElement includeXmlElement = DefinitionManager.Instance.LoadXmlDocument(type, includeDefinitionName);
                    DataTableDefinition includeDefinition = GenerateDataTableDefinition(type, includeXmlElement);
                    dataTableDefinition.IncludeDefinition = includeDefinition;
                }
            }
            #endregion

            string dataTableTableName = null;
            if (xmlElement.Attributes["TableName"] != null)
            {
                dataTableTableName = xmlElement.Attributes["TableName"].Value.Trim();
            }
            else
            {
                if (dataTableDefinition.IncludeDefinition != null)
                {
                    dataTableTableName = dataTableDefinition.IncludeDefinition.TableName;
                }
            }
            dataTableDefinition.TableName = dataTableTableName;

            if (dataTableDefinition.IncludeDefinition != null)
            {
                dataTableDefinition.SourceName = GetDataViewDefinitionSourceName(xmlElement, null); 
            }
            else
            {
                dataTableDefinition.SourceName = GetDataViewDefinitionSourceName(xmlElement, dataTableTableName);
            }

            foreach (System.Xml.XmlNode childXmlNode in xmlElement)
            {
                if (childXmlNode is System.Xml.XmlElement)
                {
                    System.Xml.XmlElement childXmlElement = childXmlNode as System.Xml.XmlElement;
                    if (childXmlElement.Name == "ViewColumn")
                    {
                        string dataColumnColumnName = childXmlElement.Attributes["ColumnName"].Value.Trim();
                        string dataColumnSourceName = dataColumnColumnName;
                        if (childXmlElement.Attributes["SourceName"] != null)
                        {
                            dataColumnSourceName = childXmlElement.Attributes["SourceName"].Value.Trim();
                        }

                        DataColumnDefinition dataColumnDefinition = new DataColumnDefinition(dataColumnColumnName, dataColumnSourceName);
                        if (childXmlElement.Attributes["AllowDBNull"] != null)
                        {
                            dataColumnDefinition.AllowDBNull = bool.Parse(childXmlElement.Attributes["AllowDBNull"].Value);
                        }
                        if (childXmlElement.Attributes["AutoIncrement"] != null)
                        {
                            dataColumnDefinition.AutoIncrement = bool.Parse(childXmlElement.Attributes["AutoIncrement"].Value);
                        }
                        if (childXmlElement.Attributes["DataType"] != null)
                        {
                            dataColumnDefinition.DbType = GetWellKnownDbType(childXmlElement.Attributes["DataType"].Value.Trim());
                        }
                        dataTableDefinition.Columns.Add(dataColumnColumnName, dataColumnDefinition);
                    }
                }
            }
            return dataTableDefinition;
        }

        /// <summary>
        /// Gets view source name 。
        /// </summary>
        /// <param name="xmlElement">xml element.</param>
        /// <param name="defaultValue">default value.</param>
        /// <returns>view source name 。</returns>
        private static string GetDataViewDefinitionSourceName(System.Xml.XmlElement xmlElement, string defaultValue)
        {
            string dataTableSourceName = defaultValue;
            if (xmlElement.Attributes["SourceName"] != null)
            {
                dataTableSourceName = xmlElement.Attributes["SourceName"].Value.Trim();
            }
            string dataViewSourceName = dataTableSourceName;
            if (xmlElement.Attributes["ViewName"] != null)
            {
                dataViewSourceName = xmlElement.Attributes["ViewName"].Value.Trim();
            }
            return dataViewSourceName;
        }

        /// <summary>
        /// Gets column mapping 。
        /// </summary>
        /// <param name="dataTableMapping">Data table mapping.</param>
        /// <param name="columnName">Column name.</param>
        /// <returns>column mapping 。</returns>
        public static System.Data.Common.DataColumnMapping GetDataColumnMapping(System.Data.Common.DataTableMapping dataTableMapping, string columnName)
        {
            return dataTableMapping.ColumnMappings.GetByDataSetColumn(columnName);
        }

        public static string GetDataColumnSourceColumn(System.Data.Common.DataTableMapping dataTableMapping, string columnName)
        {
            return dataTableMapping.ColumnMappings.GetByDataSetColumn(columnName).SourceColumn;
        }
        #endregion
        #region Common property 
        protected System.Type definitionType;
        /// <summary>
        /// Type of data object.
        /// </summary>
        public System.Type DefinitionType
        {
            get
            {
                return this.definitionType;
            }
        }

        private System.Xml.XmlElement xmlElement;
        /// <summary>
        /// xml element.
        /// </summary>
        public System.Xml.XmlElement XmlElement
        {
            get
            {
                return this.xmlElement;
            }
        }

        private DataTableDefinition dataTableDefinition;
        /// <summary>
        /// table structure definition.
        /// </summary>
        public DataTableDefinition DataTableDefinition
        {
            get
            {
                return this.dataTableDefinition;
            }
        }

        private Dictionary<string, System.Data.DbType> dbTypes;

        public Dictionary<string, System.Data.DbType> DbTypes
        {
            get { return dbTypes; }
        }

        private DataTableDefinition dataViewDefinition;
        /// <summary>
        /// table structure definition.
        /// </summary>
        public DataTableDefinition DataViewDefinition
        {
            get
            {
                if (this.dataViewDefinition == null)
                {
                    this.dataViewDefinition = GenerateDataViewDefinition(null, this.xmlElement);
                }
                return this.dataViewDefinition;
            }
        }
        
        private bool useColumnsForParameterNames = false;
        /// <summary>
        /// If the value is true, it will generate the parameter name as column name (if possible), if it's false, it will generate @p1, @p2 like that.
        /// </summary>
        public bool UseColumnsForParameterNames
        {
            get
            {
                return this.useColumnsForParameterNames;
            }
            set
            {
                this.useColumnsForParameterNames = value;
            }
        }

        private bool parseExpression = true;
        public bool ParseExpression
        {
            get
            {
                return this.parseExpression;
            }
            set
            {
                this.parseExpression = value;
            }
        }
        #endregion
        #region Constructor
        /// <summary>
        /// Constructor
        /// </summary>
        /// <param name="type">Type of data object.</param>
        public CommonCommandBuilder(System.Type type)
        {
            this.xmlElement = DefinitionManager.Instance.LoadXmlDocument(type);
            this.definitionType = type;
            this.dataTableDefinition = GenerateDataTableDefinition(type, this.xmlElement);
            this.dbTypes = GenerateDbTypes(this.dataTableDefinition);
        }

        /// <summary>
        /// Constructor
        /// </summary>
        /// <param name="type">Type of data object.</param>
        /// <param name="name">Data object name.</param>
        public CommonCommandBuilder(System.Type type, string name)
        {
            this.xmlElement = DefinitionManager.Instance.LoadXmlDocument(type, name);
            this.definitionType = type;
            this.dataTableDefinition = GenerateDataTableDefinition(type, this.xmlElement);
            this.dbTypes = GenerateDbTypes(this.dataTableDefinition);
        }

        /// <summary>
        /// Constructor
        /// </summary>
        /// <param name="xmlElement">xml element.</param>
        public CommonCommandBuilder(System.Xml.XmlElement xmlElement)
        {
            this.xmlElement = xmlElement;
            this.dataTableDefinition = GenerateDataTableDefinition(null, this.xmlElement);
            this.dbTypes = GenerateDbTypes(this.dataTableDefinition);
        }
        #endregion
        #region Common methods
        /// <summary>
        /// Gets table alias name 。
        /// </summary>
        /// <returns>table alias name 。</returns>
        public virtual string GetDataTableSourceAlias()
        {
            string dataTableTableName = xmlElement.Attributes["TableName"].Value.Trim();
            string dataTableSourceName = dataTableTableName;
            if (xmlElement.Attributes["SourceName"] != null)
            {
                dataTableSourceName = xmlElement.Attributes["SourceName"].Value.Trim();
            }
            string dataTableSourceAlias = dataTableSourceName;
            if (xmlElement.Attributes["SourceAlias"] != null)
            {
                dataTableSourceAlias = xmlElement.Attributes["SourceAlias"].Value.Trim();
            }
            return dataTableSourceAlias;
        }

        /// <summary>
        /// Create "Delete "command text.
        /// </summary>
        /// <returns>the "Delete" command.</returns>
        public virtual string BuildDeleteCommandText()
        {
            // Create "Delete "command text.
            return string.Format("DELETE FROM {0}", this.dataTableDefinition.SourceName);
        }

        /// <summary>
        /// Create "Select"  command text.
        /// </summary>
        /// <param name="dataTableMapping">mapping.</param>
        /// <returns>"Select"  command text.</returns>
        public virtual string BuildSelectCommandText(out System.Data.Common.DataTableMapping dataTableMapping)
        {
            if (xmlElement != null)
            {
                if (HasIncludeDefinition(xmlElement))
                {
                    return BuildIncludeDefinitionSelectCommandText(out dataTableMapping);
                }
                else
                {
                    string fromCommandText = BuildFromCommandText(this.xmlElement, out dataTableMapping);

                    System.Text.StringBuilder selectCommandTextStringBuilder = new StringBuilder();
                    selectCommandTextStringBuilder.Append("SELECT ");
                    foreach (System.Xml.XmlNode childXmlNode in xmlElement)
                    {
                        if (childXmlNode is System.Xml.XmlElement)
                        {
                            System.Xml.XmlElement childXmlElement = childXmlNode as System.Xml.XmlElement;
                            if (childXmlElement.Name == "DataColumn")
                            {
                                string dataColumnColumnName = childXmlElement.Attributes["ColumnName"].Value.Trim();

                                AppendColumnCommandText(dataColumnColumnName, childXmlElement, ref selectCommandTextStringBuilder, ref dataTableMapping);
                                // Add comma as a splitter.
                                selectCommandTextStringBuilder.Append(", ");
                            }
                        }
                    }
                    // remove the comma added last.
                    RemoveEnd(ref selectCommandTextStringBuilder, 2);

                    // Add select table command text.
                    selectCommandTextStringBuilder.Append(fromCommandText);
                    // Add addtion Command.
                    AppendAddtionCommandText(this.xmlElement, ref selectCommandTextStringBuilder);

                    return selectCommandTextStringBuilder.ToString();
                }
            }
            else
            {
                // TODO: use [Attribute] to define Table/Column mapping.
                string fromCommandText = BuildFromCommandText(this.dataTableDefinition, out dataTableMapping);

                System.Text.StringBuilder selectCommandTextStringBuilder = new StringBuilder();
                selectCommandTextStringBuilder.Append("SELECT ");
                foreach (DataColumnDefinition dataColumnDefinition in this.dataTableDefinition.Columns.Values)
                {
                    string dataColumnColumnName = dataColumnDefinition.ColumnName;
                    AppendColumnCommandText(dataColumnColumnName, dataColumnDefinition, ref selectCommandTextStringBuilder, ref dataTableMapping);
                    // Add comma as a splitter.
                    selectCommandTextStringBuilder.Append(", ");
                }
                // remove the comma added last.
                RemoveEnd(ref selectCommandTextStringBuilder, 2);

                // Add select table command text.
                selectCommandTextStringBuilder.Append(fromCommandText);

                return selectCommandTextStringBuilder.ToString();
            }
        }

        /// <summary>
        /// Create "Select"  command text.
        /// </summary>
        /// <param name="columnNames">An array of Columns.</param>
        /// <param name="dataTableMapping">mapping.</param>
        /// <returns>"Select"  command text.</returns>
        public virtual string BuildSelectCommandText(string[] columnNames, out System.Data.Common.DataTableMapping dataTableMapping)
        {
            if (xmlElement != null)
            {
                if (HasIncludeDefinition(xmlElement))
                {
                    return BuildIncludeDefinitionSelectCommandText(columnNames, out dataTableMapping);
                }
                else
                {
                    string fromCommandText = BuildFromCommandText(this.xmlElement, out dataTableMapping);

                    System.Text.StringBuilder selectCommandTextStringBuilder = new StringBuilder();
                    selectCommandTextStringBuilder.Append("SELECT ");
                    foreach (System.Xml.XmlNode childXmlNode in xmlElement)
                    {
                        if (childXmlNode is System.Xml.XmlElement)
                        {
                            System.Xml.XmlElement childXmlElement = childXmlNode as System.Xml.XmlElement;
                            if (childXmlElement.Name == "DataColumn")
                            {
                                string dataColumnColumnName = childXmlElement.Attributes["ColumnName"].Value.Trim();

                                if (ContainsValue(columnNames, dataColumnColumnName))
                                {
                                    AppendColumnCommandText(dataColumnColumnName, childXmlElement, ref selectCommandTextStringBuilder, ref dataTableMapping);
                                    // Add comma as a splitter.
                                    selectCommandTextStringBuilder.Append(", ");
                                }
                            }
                        }
                    }
                    // remove the comma added last.
                    RemoveEnd(ref selectCommandTextStringBuilder, 2);

                    // Add select table command text.
                    selectCommandTextStringBuilder.Append(fromCommandText);
                    // Add addtion Command.
                    AppendAddtionCommandText(this.xmlElement, ref selectCommandTextStringBuilder);

                    return selectCommandTextStringBuilder.ToString();
                }
            }
            else
            {
                // TODO: use [Attribute] to define Table/Column mapping.
                string fromCommandText = BuildFromCommandText(this.dataTableDefinition, out dataTableMapping);

                System.Text.StringBuilder selectCommandTextStringBuilder = new StringBuilder();
                selectCommandTextStringBuilder.Append("SELECT ");
                foreach (DataColumnDefinition dataColumnDefinition in this.dataTableDefinition.Columns.Values)
                {
                    string dataColumnColumnName = dataColumnDefinition.ColumnName;

                    if (ContainsValue(columnNames, dataColumnColumnName))
                    {
                        AppendColumnCommandText(dataColumnColumnName, dataColumnDefinition, ref selectCommandTextStringBuilder, ref dataTableMapping);
                        // Add comma as a splitter.
                        selectCommandTextStringBuilder.Append(", ");
                    }
                }
                // remove the comma added last.
                RemoveEnd(ref selectCommandTextStringBuilder, 2);

                // Add select table command text.
                selectCommandTextStringBuilder.Append(fromCommandText);

                return selectCommandTextStringBuilder.ToString();
            }
        }

        /// <summary>
        /// Create Data table mapping.
        /// </summary>
        /// <param name="xmlElement">xml element.</param>
        /// <param name="dataTableMapping">Data table mapping.</param>
        protected static void BuildDataTableMapping(System.Xml.XmlElement xmlElement, out System.Data.Common.DataTableMapping dataTableMapping)
        {
            string dataTableTableName = xmlElement.Attributes["TableName"].Value.Trim();
            string dataTableSourceName = dataTableTableName;
            if (xmlElement.Attributes["SourceName"] != null)
            {
                dataTableSourceName = xmlElement.Attributes["SourceName"].Value.Trim();
            }
            string dataTableSourceAlias = dataTableSourceName;
            if (xmlElement.Attributes["SourceAlias"] != null)
            {
                dataTableSourceAlias = xmlElement.Attributes["SourceAlias"].Value.Trim();
            }

            if (dataTableSourceName == dataTableSourceAlias)
            {
                dataTableMapping = new System.Data.Common.DataTableMapping(dataTableSourceName, dataTableTableName);
            }
            else
            {
                dataTableMapping = new System.Data.Common.DataTableMapping(dataTableSourceAlias, dataTableTableName);
            }
        }

        private void BuildDataTableMapping(DataTableDefinition dataTableDefinition, out System.Data.Common.DataTableMapping dataTableMapping)
        {
            string dataTableTableName = dataTableDefinition.TableName;
            string dataTableSourceName = dataTableTableName;
            if (!string.IsNullOrEmpty(dataTableDefinition.SourceName))
            {
                dataTableSourceName = dataTableDefinition.SourceName;
            }
            string dataTableSourceAlias = dataTableSourceName;
            dataTableMapping = new System.Data.Common.DataTableMapping(dataTableSourceName, dataTableTableName);
        }

        /// <summary>
        /// append column mapping 。
        /// </summary>
        /// <param name="dataColumnColumnName">Column name.</param>
        /// <param name="childXmlElement">xml element.</param>
        /// <param name="dataTableMapping">Data table mapping.</param>
        protected static void AppendColumnMapping(string dataColumnColumnName, System.Xml.XmlElement childXmlElement, ref System.Data.Common.DataTableMapping dataTableMapping)
        {
            string dataColumnSourceName = dataColumnColumnName;
            if (childXmlElement.Attributes["SourceName"] != null)
            {
                dataColumnSourceName = childXmlElement.Attributes["SourceName"].Value.Trim();
            }
            string dataColumnSourceAlias = dataColumnSourceName;
            if (childXmlElement.Attributes["SourceAlias"] != null)
            {
                dataColumnSourceAlias = childXmlElement.Attributes["SourceAlias"].Value.Trim();
            }

            if (dataColumnSourceName == dataColumnSourceAlias)
            {
                dataTableMapping.ColumnMappings.Add(dataColumnSourceName, dataColumnColumnName);
            }
            else
            {
                dataTableMapping.ColumnMappings.Add(dataColumnSourceAlias, dataColumnColumnName);
            }
        }

        protected static void AppendColumnMapping(string dataColumnColumnName, DataColumnDefinition dataColumnDefinition, ref System.Data.Common.DataTableMapping dataTableMapping)
        {
            string dataColumnSourceName = dataColumnColumnName;
            if (!string.IsNullOrEmpty(dataColumnDefinition.SourceName))
            {
                dataColumnSourceName = dataColumnDefinition.SourceName;
            }
            dataTableMapping.ColumnMappings.Add(dataColumnSourceName, dataColumnColumnName);
        }


        private System.Data.Common.DataTableMapping _defaultDataTableMapping;

        public System.Data.Common.DataTableMapping DefaultDataTableMapping
        {
            get
            {
                if (this._defaultDataTableMapping == null)
                {
                    this._defaultDataTableMapping = GetDataTableMapping();
                }
                return _defaultDataTableMapping;
            }
        }
        
        /// <summary>
        /// Gets Data table mapping.
        /// </summary>
        /// <returns>Data table mapping.</returns>
        public virtual System.Data.Common.DataTableMapping GetDataTableMapping()
        {
            System.Data.Common.DataTableMapping dataTableMapping;
            if (this.xmlElement != null)
            {
                BuildDataTableMapping(this.xmlElement, out dataTableMapping);
                foreach (System.Xml.XmlNode childXmlNode in xmlElement)
                {
                    if (childXmlNode is System.Xml.XmlElement)
                    {
                        System.Xml.XmlElement childXmlElement = childXmlNode as System.Xml.XmlElement;
                        if (childXmlElement.Name == "DataColumn")
                        {
                            string dataColumnColumnName = childXmlElement.Attributes["ColumnName"].Value.Trim();
                            AppendColumnMapping(dataColumnColumnName, childXmlElement, ref dataTableMapping);
                        }
                    }
                }
            }
            else
            {
                // TODO: use [Attribute] to define Table/Column mapping.
                BuildDataTableMapping(this.dataTableDefinition, out dataTableMapping);
                foreach (DataColumnDefinition dataColumnDefinition in this.dataTableDefinition.Columns.Values)
                {
                    string dataColumnColumnName = dataColumnDefinition.ColumnName;
                    AppendColumnMapping(dataColumnColumnName, dataColumnDefinition, ref dataTableMapping);
                }
            }
            return dataTableMapping;
        }

        /// <summary>
        /// Gets Data table mapping.
        /// </summary>
        /// <param name="columnNames">An array of Columns.</param>
        /// <returns>Data table mapping.</returns>
        public virtual System.Data.Common.DataTableMapping GetDataTableMapping(string[] columnNames)
        {
            System.Data.Common.DataTableMapping dataTableMapping;
            if (this.xmlElement != null)
            {
                BuildDataTableMapping(this.xmlElement, out dataTableMapping);
                foreach (System.Xml.XmlNode childXmlNode in xmlElement)
                {
                    if (childXmlNode is System.Xml.XmlElement)
                    {
                        System.Xml.XmlElement childXmlElement = childXmlNode as System.Xml.XmlElement;
                        if (childXmlElement.Name == "DataColumn")
                        {
                            string dataColumnColumnName = childXmlElement.Attributes["ColumnName"].Value.Trim();
                            if (ContainsValue(columnNames, dataColumnColumnName))
                            {
                                AppendColumnMapping(dataColumnColumnName, childXmlElement, ref dataTableMapping);
                            }
                        }
                    }
                }
            }
            else
            {
                // TODO: use [Attribute] to define Table/Column mapping.
                BuildDataTableMapping(this.dataTableDefinition, out dataTableMapping);
                foreach (DataColumnDefinition dataColumnDefinition in this.dataTableDefinition.Columns.Values)
                {
                    string dataColumnColumnName = dataColumnDefinition.ColumnName;
                    if (ContainsValue(columnNames, dataColumnColumnName))
                    {
                        AppendColumnMapping(dataColumnColumnName, dataColumnDefinition, ref dataTableMapping);
                    }
                }
            }
            return dataTableMapping;
        }

        /// <summary>
        /// Gets primary key An array of Columns.
        /// </summary>
        /// <returns>primary key An array of Columns.</returns>
        public virtual string[] GetPrimaryKeyNames()
        {
            List<string> primaryKeyList = new List<string>();
            if (this.dataTableDefinition != null)
            {
                primaryKeyList = this.dataTableDefinition.PrimaryKeys;
            }
            else
            {
                foreach (System.Xml.XmlNode childXmlNode in xmlElement)
                {
                    if (childXmlNode is System.Xml.XmlElement)
                    {
                        System.Xml.XmlElement childXmlElement = childXmlNode as System.Xml.XmlElement;
                        if (childXmlElement.Name == "DataColumn")
                        {
                            if (childXmlElement.Attributes["PrimaryKey"] != null)
                            {
                                bool primaryKey = bool.Parse(childXmlElement.Attributes["PrimaryKey"].Value);
                                if (primaryKey)
                                {
                                    string columnName = childXmlElement.Attributes["ColumnName"].Value.Trim();
                                    primaryKeyList.Add(columnName);
                                }
                            }
                        }
                    }
                }
            }
            string[] primaryKeys = new string[primaryKeyList.Count];
            primaryKeyList.CopyTo(primaryKeys);
            return primaryKeys;
        }

        /// <summary>
        /// Gets primary key column index array.
        /// </summary>
        /// <returns>primary key column index array.</returns>
        public virtual int[] GetPrimaryKeysIndex()
        {
            List<int> primaryKeyIndexList = new List<int>();
            int index = 0;
            if (xmlElement != null)
            {
                foreach (System.Xml.XmlNode childXmlNode in xmlElement)
                {
                    if (childXmlNode is System.Xml.XmlElement)
                    {
                        System.Xml.XmlElement childXmlElement = childXmlNode as System.Xml.XmlElement;
                        if (childXmlElement.Name == "DataColumn")
                        {
                            if (childXmlElement.Attributes["PrimaryKey"] != null)
                            {
                                bool primaryKey = bool.Parse(childXmlElement.Attributes["PrimaryKey"].Value);
                                if (primaryKey)
                                {
                                    primaryKeyIndexList.Add(index);
                                }
                            }
                            index++;
                        }
                    }
                }
            }
            else
            {
                foreach (DataColumnDefinition dataColumnDefinition in this.dataTableDefinition.Columns.Values)
                {
                    if (dataColumnDefinition.IsPrimaryKey)
                    {
                        primaryKeyIndexList.Add(index);
                    }
                    index++;
                }
            }
            int[] primaryKeysIndex = new int[primaryKeyIndexList.Count];
            primaryKeyIndexList.CopyTo(primaryKeysIndex);
            return primaryKeysIndex;
        }

        /// <summary>
        /// Gets autoincrement Column name.
        /// </summary>
        /// <returns>autoincrement Column name.</returns>
        public virtual string GetAutoIncrementColumn()
        {
            if (this.xmlElement != null)
            {
                foreach (System.Xml.XmlNode childXmlNode in xmlElement)
                {
                    if (childXmlNode is System.Xml.XmlElement)
                    {
                        System.Xml.XmlElement childXmlElement = childXmlNode as System.Xml.XmlElement;
                        if (childXmlElement.Name == "DataColumn")
                        {
                            if (childXmlElement.Attributes["AutoIncrement"] != null)
                            {
                                bool autoIncrement = bool.Parse(childXmlElement.Attributes["AutoIncrement"].Value);
                                if (autoIncrement)
                                {
                                    string columnName = childXmlElement.Attributes["ColumnName"].Value.Trim();
                                    return columnName;
                                }
                            }
                        }
                    }
                }
            }
            else
            {
                foreach (DataColumnDefinition dataColumnDefinition in this.dataTableDefinition.Columns.Values)
                {
                    if (dataColumnDefinition.AutoIncrement)
                    {
                        return dataColumnDefinition.ColumnName;
                    }
                }
            }
            return null;
        }

        /// <summary>
        /// Gets autoincrement column index.
        /// </summary>
        /// <returns>autoincrement column index.</returns>
        public virtual int GetAutoIncrementColumnIndex()
        {
            int index = 0;
            if (this.xmlElement != null)
            {
                foreach (System.Xml.XmlNode childXmlNode in xmlElement)
                {
                    if (childXmlNode is System.Xml.XmlElement)
                    {
                        System.Xml.XmlElement childXmlElement = childXmlNode as System.Xml.XmlElement;
                        if (childXmlElement.Name == "DataColumn")
                        {
                            if (childXmlElement.Attributes["AutoIncrement"] != null)
                            {
                                bool autoIncrement = bool.Parse(childXmlElement.Attributes["AutoIncrement"].Value);
                                if (autoIncrement)
                                {
                                    return index;
                                }
                            }
                            index++;
                        }
                    }
                }
            }
            else
            {
                foreach (DataColumnDefinition dataColumnDefinition in this.dataTableDefinition.Columns.Values)
                {
                    if (dataColumnDefinition.AutoIncrement)
                    {
                        return index;
                    }
                    index++;
                }
            }
            return -1;
        }

        /// <summary>
        /// check column A value that indicates whether null values are allowed in this column for rows that belong to the table.
        /// </summary>
        /// <param name="columnName">Column name.</param>
        /// <returns>允许为空返回true，否则返回false。</returns>
        public virtual bool ColumnAllowDBNull(string columnName)
        {
            if (ContainsValue(GetPrimaryKeyNames(), columnName))
            {
                // primary key is not allow to be null.
                return false;
            }
            else
            {
                DataColumnDefinition dataColumnDefinition = this.dataTableDefinition.Columns[columnName];
                return dataColumnDefinition.AllowDBNull;
            }
        }

        private Dictionary<string, System.Data.DbType> GenerateDbTypes(DataTableDefinition dataTableDefinition)
        {
            Dictionary<string, System.Data.DbType> dbTypes = new Dictionary<string, System.Data.DbType>();
            foreach (KeyValuePair<string, DataColumnDefinition> column in dataTableDefinition.Columns)
            {
                dbTypes.Add(column.Key, column.Value.DbType);
            }
            return dbTypes;
        }

        /// <summary>
        /// Create common Condition Command.
        /// </summary>
        /// <param name="dataTableMapping">mapping.</param>
        /// <param name="commonCommand">Common Command.</param>
        /// <param name="commandTextStringBuilder">string builder.</param>
        /// <param name="parameterNo">parameter no.</param>
        public virtual void BuildNormalConditionCommand(System.Data.Common.DataTableMapping dataTableMapping, CommonCommand commonCommand, ref System.Text.StringBuilder commandTextStringBuilder, ref int parameterNo)
        {
            string[] primaryKeyNames = GetPrimaryKeyNames();
            if ((primaryKeyNames != null) && (primaryKeyNames.Length > 0))
            {
                BuildNormalConditionCommand(primaryKeyNames, dataTableMapping, commonCommand, ref commandTextStringBuilder, ref parameterNo);
            }
        }

        private void BuildNormalConditionCommand(string[] primaryKeyNames, System.Data.Common.DataTableMapping dataTableMapping, CommonCommand commonCommand, ref StringBuilder commandTextStringBuilder, ref int parameterNo)
        {
            foreach (string primaryKey in primaryKeyNames)
            {
                string parameterName = string.Format(PARAMETERNAMEFORMAT, parameterNo);
                parameterNo++;
                if (useColumnsForParameterNames)
                {
                    parameterName = string.Format("@Original_{0}", primaryKey);
                }

                System.Data.Common.DataColumnMapping dataColumnMapping = GetDataColumnMapping(dataTableMapping, primaryKey);
                DataColumnDefinition dataColumnDefinition = this.dataTableDefinition.Columns[primaryKey];
               
                commonCommand.Parameters.Add(new CommonParameter(parameterName, dataColumnDefinition.DbType, System.Data.ParameterDirection.Input, dataColumnMapping.DataSetColumn, System.Data.DataRowVersion.Original, false));

#if (PocketPC || Smartphone || WindowsCE)
                commandTextStringBuilder.Append(string.Format("{0} = {1}", dataColumnDefinition.SourceName, parameterName));
#else
                commandTextStringBuilder.AppendFormat("{0} = {1}", dataColumnDefinition.SourceName, parameterName);
#endif
                commandTextStringBuilder.Append(" AND ");
            }
            RemoveEnd(ref commandTextStringBuilder, 5);
        }

        /// <summary>
        /// Create check concurrency Condition Command.
        /// </summary>
        /// <param name="dataTableMapping">mapping.</param>
        /// <param name="commonCommand">Common Command.</param>
        /// <param name="commandTextStringBuilder">string builder.</param>
        /// <param name="parameterNo">parameter no.</param>
        public virtual void BuildCheckConcurrencyConditionCommand(System.Data.Common.DataTableMapping dataTableMapping, CommonCommand commonCommand, ref System.Text.StringBuilder commandTextStringBuilder, ref int parameterNo)
        {
            if (useColumnsForParameterNames)
            {
                foreach (System.Data.Common.DataColumnMapping dataColumnMapping in dataTableMapping.ColumnMappings)
                {
                    string columnName = dataColumnMapping.DataSetColumn;
                    DataColumnDefinition dataColumnDefinition = this.dataTableDefinition.Columns[columnName];
                    bool allowDBNull = ColumnAllowDBNull(columnName);
                    if (allowDBNull)
                    {
                        string isNullParameterName = string.Format("@IsNull_{0}", columnName);
                        string parameterName = string.Format("@Original_{0}", columnName);
                       
                        commonCommand.Parameters.Add(new CommonParameter(isNullParameterName, System.Data.DbType.Int32, System.Data.ParameterDirection.Input, dataColumnMapping.DataSetColumn, System.Data.DataRowVersion.Original, true));
                        commonCommand.Parameters.Add(new CommonParameter(parameterName, dataColumnDefinition.DbType, System.Data.ParameterDirection.Input, dataColumnMapping.DataSetColumn, System.Data.DataRowVersion.Original, false));

#if (PocketPC || Smartphone || WindowsCE)
                        commandTextStringBuilder.Append(string.Format("(({0} = 1 AND {1} IS NULL) OR ({1} = {2}))", isNullParameterName, dataColumnDefinition.SourceName, parameterName));
#else
                        commandTextStringBuilder.AppendFormat("(({0} = 1 AND {1} IS NULL) OR ({1} = {2}))", isNullParameterName, dataColumnDefinition.SourceName, parameterName);
#endif
                        commandTextStringBuilder.Append(" AND ");
                    }
                    else
                    {
                        string parameterName = string.Format("@Original_{0}", columnName);
                       
                        commonCommand.Parameters.Add(new CommonParameter(parameterName, dataColumnDefinition.DbType, System.Data.ParameterDirection.Input, dataColumnMapping.DataSetColumn, System.Data.DataRowVersion.Original, false));

#if (PocketPC || Smartphone || WindowsCE)
                        commandTextStringBuilder.Append(string.Format("({0} = {1})", dataColumnDefinition.SourceName, parameterName));
#else
                        commandTextStringBuilder.AppendFormat("({0} = {1})", dataColumnDefinition.SourceName, parameterName);
#endif
                        commandTextStringBuilder.Append(" AND ");
                    }
                }
                RemoveEnd(ref commandTextStringBuilder, 5);
            }
            else
            {
                foreach (System.Data.Common.DataColumnMapping dataColumnMapping in dataTableMapping.ColumnMappings)
                {
                    string columnName = dataColumnMapping.DataSetColumn;
                    DataColumnDefinition dataColumnDefinition = this.dataTableDefinition.Columns[columnName];
                    bool allowDBNull = ColumnAllowDBNull(columnName);
                    if (allowDBNull)
                    {
                        string isNullParameterName = string.Format(PARAMETERNAMEFORMAT, parameterNo);
                        parameterNo++;
                        string parameterName = string.Format(PARAMETERNAMEFORMAT, parameterNo);
                        parameterNo++;
                       
                        commonCommand.Parameters.Add(new CommonParameter(isNullParameterName, System.Data.DbType.Int32, System.Data.ParameterDirection.Input, dataColumnMapping.DataSetColumn, System.Data.DataRowVersion.Original, true));
                        commonCommand.Parameters.Add(new CommonParameter(parameterName, dataColumnDefinition.DbType, System.Data.ParameterDirection.Input, dataColumnMapping.DataSetColumn, System.Data.DataRowVersion.Original, false));

#if (PocketPC || Smartphone || WindowsCE)
                        commandTextStringBuilder.Append(string.Format("(({0} = 1 AND {1} IS NULL) OR ({1} = {2}))", isNullParameterName, dataColumnDefinition.SourceName, parameterName));
#else
                        commandTextStringBuilder.AppendFormat("(({0} = 1 AND {1} IS NULL) OR ({1} = {2}))", isNullParameterName, dataColumnDefinition.SourceName, parameterName);
#endif
                        commandTextStringBuilder.Append(" AND ");
                    }
                    else
                    {
                        string parameterName = string.Format(PARAMETERNAMEFORMAT, parameterNo);
                        parameterNo++;
                       
                        commonCommand.Parameters.Add(new CommonParameter(parameterName, dataColumnDefinition.DbType, System.Data.ParameterDirection.Input, dataColumnMapping.DataSetColumn, System.Data.DataRowVersion.Original, false));

#if (PocketPC || Smartphone || WindowsCE)
                        commandTextStringBuilder.Append(string.Format("({0} = {1})", dataColumnDefinition.SourceName, parameterName));
#else
                        commandTextStringBuilder.AppendFormat("({0} = {1})", dataColumnDefinition.SourceName, parameterName);
#endif
                        commandTextStringBuilder.Append(" AND ");
                    }
                }
                RemoveEnd(ref commandTextStringBuilder, 5);
            }
        }

        /// <summary>
        /// Create Condition Command.
        /// </summary>
        /// <param name="dataTableMapping">mapping.</param>
        /// <param name="checkConcurrency">Check concurrency.</param>
        /// <returns>Condition Command.</returns>
        public virtual CommonCommand BuildConditionCommand(System.Data.Common.DataTableMapping dataTableMapping, bool checkConcurrency, ref int parameterNo)
        {
            CommonCommand commonCommand = new CommonCommand();
            System.Text.StringBuilder conditionStringBuilder = new StringBuilder();
            conditionStringBuilder.Append("WHERE ");
            if (checkConcurrency)
            {
                // Check concurrency.
                BuildCheckConcurrencyConditionCommand(dataTableMapping, commonCommand, ref conditionStringBuilder, ref parameterNo);
            }
            else
            {
                string[] primaryKeyNames = GetPrimaryKeyNames();
                if ((primaryKeyNames != null) && (primaryKeyNames.Length > 0))
                {
                    BuildNormalConditionCommand(primaryKeyNames, dataTableMapping, commonCommand, ref conditionStringBuilder, ref parameterNo);
                }
                else
                {
                    // Check concurrency.
                    BuildCheckConcurrencyConditionCommand(dataTableMapping, commonCommand, ref conditionStringBuilder, ref parameterNo);
                }
            }
            commonCommand.CommandText = conditionStringBuilder.ToString();
            return commonCommand;
        }

        /// <summary>
        /// generate query autoincrement number Command.
        /// </summary>
        /// <param name="dataTableMapping">mapping.</param>
        /// <param name="autoIncrementStringBuilder">string builder.</param>
        /// <param name="dataColumnMapping">column mapping 。</param>
        /// <param name="dataColumnDefinition">column definition.</param>
        public virtual void GenerateAutoIncrementCommand(System.Data.Common.DataTableMapping dataTableMapping, ref System.Text.StringBuilder autoIncrementStringBuilder, System.Data.Common.DataColumnMapping dataColumnMapping, DataColumnDefinition dataColumnDefinition)
        {
            if (dataTableMapping.SourceTable == this.dataTableDefinition.SourceName)
            {
                if (dataColumnMapping.SourceColumn == dataColumnDefinition.SourceName)
                {
#if (PocketPC || Smartphone || WindowsCE)
                    autoIncrementStringBuilder.Append(string.Format("SELECT {0} FROM {1} WHERE ({0} = SCOPE_IDENTITY())", dataColumnDefinition.SourceName, this.dataTableDefinition.SourceName));
#else
                    autoIncrementStringBuilder.AppendFormat("SELECT {0} FROM {1} WHERE ({0} = SCOPE_IDENTITY())", dataColumnDefinition.SourceName, this.dataTableDefinition.SourceName);
#endif
                }
                else
                {
#if (PocketPC || Smartphone || WindowsCE)
                    autoIncrementStringBuilder.Append(string.Format("SELECT {0} AS {1} FROM {2} WHERE ({0} = SCOPE_IDENTITY())", dataColumnDefinition.SourceName, dataColumnMapping.SourceColumn, this.dataTableDefinition.SourceName));
#else
                    autoIncrementStringBuilder.AppendFormat("SELECT {0} AS {1} FROM {2} WHERE ({0} = SCOPE_IDENTITY())", dataColumnDefinition.SourceName, dataColumnMapping.SourceColumn, this.dataTableDefinition.SourceName);
#endif
                }
            }
            else
            {
                if (dataColumnMapping.SourceColumn == dataColumnDefinition.SourceName)
                {
#if (PocketPC || Smartphone || WindowsCE)
                    autoIncrementStringBuilder.Append(string.Format("SELECT {0} FROM {1} {2} WHERE ({0} = SCOPE_IDENTITY())", dataColumnDefinition.SourceName, this.dataTableDefinition.SourceName, dataTableMapping.SourceTable));
#else
                    autoIncrementStringBuilder.AppendFormat("SELECT {0} FROM {1} {2} WHERE ({0} = SCOPE_IDENTITY())", dataColumnDefinition.SourceName, this.dataTableDefinition.SourceName, dataTableMapping.SourceTable);
#endif
                }
                else
                {
#if (PocketPC || Smartphone || WindowsCE)
                    autoIncrementStringBuilder.Append(string.Format("SELECT {0} AS {1} FROM {2} {3} WHERE ({0} = SCOPE_IDENTITY())", dataColumnDefinition.SourceName, dataColumnMapping.SourceColumn, this.dataTableDefinition.SourceName, dataTableMapping.SourceTable));
#else
                    autoIncrementStringBuilder.AppendFormat("SELECT {0} AS {1} FROM {2} {3} WHERE ({0} = SCOPE_IDENTITY())", dataColumnDefinition.SourceName, dataColumnMapping.SourceColumn, this.dataTableDefinition.SourceName, dataTableMapping.SourceTable);
#endif
                }
            }
        }

        /// <summary>
        /// addtion "Condition" Command.
        /// </summary>
        /// <remarks>
        /// pay attention to the parameter name.
        /// </remarks>
        /// <param name="commonCommand">Common Command.</param>
        /// <param name="conditionCommand">Condition Command.</param>
        /// <returns>addtion Condition Command Common Command.</returns>
        public virtual CommonCommand AppendConditionCommand(CommonCommand commonCommand, CommonCommand conditionCommand)
        {
            commonCommand.CommandText = string.Format("{0}{1}{2}", commonCommand.CommandText, CONDITIONCOMMANDAPPENDTEXT, conditionCommand.CommandText);
            // addtion parameters.
            foreach (CommonParameter commonParameter in conditionCommand.Parameters)
            {
                commonCommand.Parameters.Add(commonParameter);
            }
            return commonCommand;
        }


        /// <summary>
        /// addtion "Condition" Command.
        /// </summary>
        /// <remarks>
        /// pay attention to the parameter name.
        /// </remarks>
        /// <param name="commonCommand">Common Command.</param>
        /// <param name="conditionCommand">Condition Command.</param>
        /// <returns>addtion Condition Command Common Command.</returns>
        public virtual CommonCommand AppendMappingCommand(CommonCommand commonCommand, CommonCommand conditionCommand)
        {
            if (conditionCommand != null)
            {
                string conditionCommandText = conditionCommand.CommandText;
                conditionCommandText = AdvancedReplaceMappingCommandText(conditionCommandText);
                commonCommand.CommandText = string.Format("{0}{1}{2}", commonCommand.CommandText, CONDITIONCOMMANDAPPENDTEXT, conditionCommandText);
                // addtion parameters.
                foreach (CommonParameter commonParameter in conditionCommand.Parameters)
                {
                    commonCommand.Parameters.Add(commonParameter);
                }
            }
            return commonCommand;
        }

        private string AdvancedReplaceMappingCommandText(string conditionCommandText)
        {
            if ((ParseExpression) && (StringExtension.Contains(conditionCommandText, "'")))
            {
                // column name in string.
                Parser parser = new Parser(conditionCommandText);
                parser.ParseLevel = ParseLevel.Character;
                List<Expression> expressions = parser.Parse();
                if ((expressions != null) && (expressions.Count > 0))
                {
                    StringBuilder stringBuilder = new StringBuilder();
                    int current = 0;
                    foreach (Expression expression in expressions)
                    {
                        if (expression.Begin.Position > current)
                        {
                            string commandExpression = conditionCommandText.Substring(current, expression.Begin.Position - current);
                            stringBuilder.Append(ReplaceMappingCommandText(commandExpression));
                            stringBuilder.Append(conditionCommandText.Substring(expression.Begin.Position, expression.End.Position - expression.Begin.Position));
                            current = expression.End.Position;
                        }
                        else
                        {
                            stringBuilder.Append(conditionCommandText.Substring(expression.Begin.Position, expression.End.Position - expression.Begin.Position));
                            current = expression.End.Position;
                        }
                    }
                    if (current < conditionCommandText.Length)
                    {
                        string commandExpression = conditionCommandText.Substring(current, conditionCommandText.Length - current);
                        stringBuilder.Append(ReplaceMappingCommandText(commandExpression));
                    }
                    conditionCommandText = stringBuilder.ToString();
                }
                else
                {
                    conditionCommandText = ReplaceMappingCommandText(conditionCommandText);
                }
            }
            else
            {
                conditionCommandText = ReplaceMappingCommandText(conditionCommandText);
            }
            return conditionCommandText;
        }

        private string ReplaceMappingCommandText(string conditionCommandText)
        {
            string tableName = string.Format(".[{0}]", this.dataTableDefinition.TableName);
            string sourceName = this.dataTableDefinition.SourceName;
            conditionCommandText = conditionCommandText.Replace(tableName, sourceName);
            foreach (string columnName in this.dataTableDefinition.Columns.Keys)
            {
                string columnColumnName = string.Format("[{0}]", columnName);
                string columnSourceName = this.dataTableDefinition.Columns[columnName].SourceName;
                conditionCommandText = conditionCommandText.Replace(columnColumnName, columnSourceName);
            }
            return conditionCommandText;
        }

        /// <summary>
        /// Gets mapping ReplacedCommon Command.
        /// </summary>
        /// <param name="commonCommand">Common Command.</param>
        /// <returns>mapping ReplacedCommon Command.</returns>
        public virtual CommonCommand GetMappingCommand(CommonCommand commonCommand)
        {
            string commandText = commonCommand.CommandText;
            commandText = AdvancedReplaceMappingCommandText(commandText);
            CommonCommand mappingCommand = new CommonCommand();
            mappingCommand.CommandText = commandText;
            // addtion parameters.
            foreach (CommonParameter commonParameter in commonCommand.Parameters)
            {
                mappingCommand.Parameters.Add(commonParameter);
            }
            return mappingCommand;
        }


        #region Select 
        /// <summary>
        /// Gets "Select"  Command.
        /// </summary>
        /// <param name="dataTableMapping">Data table mapping.</param>
        /// <returns>"Select"  Command.</returns>
        public CommonCommand GetSelectCommand(out System.Data.Common.DataTableMapping dataTableMapping)
        {
            CommonCommand commonCommand = new CommonCommand();
            string commandText = BuildSelectCommandText(out dataTableMapping);
            commonCommand.CommandText = commandText;
            return commonCommand;
        }

        /// <summary>
        /// Gets "Select"  Command.
        /// </summary>
        /// <param name="columnNames">An array of Columns.</param>
        /// <param name="dataTableMapping">Data table mapping.</param>
        /// <returns>"Select"  Command.</returns>
        public CommonCommand GetSelectCommand(string[] columnNames, out System.Data.Common.DataTableMapping dataTableMapping)
        {
            CommonCommand commonCommand = new CommonCommand();
            string commandText = BuildSelectCommandText(columnNames, out dataTableMapping);
            commonCommand.CommandText = commandText;
            return commonCommand;
        }

        /// <summary>
        /// Gets "Select"  Command.
        /// </summary>
        /// <param name="conditionCommand">"Condition" Command.</param>
        /// <param name="dataTableMapping">Data table mapping.</param>
        /// <returns>"Select"  Command.</returns>
        public CommonCommand GetSelectCommand(CommonCommand conditionCommand, out System.Data.Common.DataTableMapping dataTableMapping)
        {
            CommonCommand commonCommand = new CommonCommand();
            string commandText = BuildSelectCommandText(out dataTableMapping);
            commonCommand.CommandText = commandText;
            return AppendMappingCommand(commonCommand, conditionCommand);
        }

        /// <summary>
        /// Gets "Select"  Command.
        /// </summary>
        /// <param name="columnNames">An array of Columns.</param>
        /// <param name="conditionCommand">"Condition" Command.</param>
        /// <param name="dataTableMapping">Data table mapping.</param>
        /// <returns>"Select"  Command.</returns>
        public CommonCommand GetSelectCommand(string[] columnNames, CommonCommand conditionCommand, out System.Data.Common.DataTableMapping dataTableMapping)
        {
            CommonCommand commonCommand = new CommonCommand();
            string commandText = BuildSelectCommandText(columnNames, out dataTableMapping);
            commonCommand.CommandText = commandText;
            return AppendMappingCommand(commonCommand, conditionCommand);
        }
        #endregion
        #region Delete 
        /// <summary>
        /// Gets the "Delete" command.
        /// </summary>
        /// <returns>the "Delete" command.</returns>
        public CommonCommand GetDeleteCommand()
        {
            CommonCommand commonCommand = new CommonCommand();
            // Create Delete command text.
            commonCommand.CommandText = BuildDeleteCommandText();
            return commonCommand;
        }

        /// <summary>
        /// Gets the "Delete" command.
        /// </summary>
        /// <param name="dataTableMapping">Data table mapping.</param>
        /// <param name="checkConcurrency">Check concurrency.</param>
        /// <returns>the "Delete" command.</returns>
        public CommonCommand GetDeleteCommand(System.Data.Common.DataTableMapping dataTableMapping, bool checkConcurrency)
        {
            int parameterNo = 1;
            CommonCommand commonCommand = GetDeleteCommand();
            CommonCommand conditionCommand = BuildConditionCommand(this.DefaultDataTableMapping, checkConcurrency, ref parameterNo);
            return AppendConditionCommand(commonCommand, conditionCommand);
        }

        /// <summary>
        /// Gets the "Delete" command.
        /// </summary>
        /// <param name="conditionCommand">"Condition" Command.</param>
        /// <returns>the "Delete" command.</returns>
        public CommonCommand GetDeleteCommand(CommonCommand conditionCommand)
        {
            CommonCommand commonCommand = new CommonCommand();
            string commandText = BuildDeleteCommandText();
            commonCommand.CommandText = commandText;
            return AppendMappingCommand(commonCommand, conditionCommand);
        }
        #endregion
        #region Insert 
        /// <summary>
        /// Gets the "Insert" command.
        /// </summary>
        /// <param name="dataTableMapping">Data table mapping.</param>
        /// <param name="getAutoIncrementValue">Gets the value of autoincrement.</param>
        /// <returns>the "Insert" command.</returns>
        public CommonCommand GetInsertCommand(System.Data.Common.DataTableMapping dataTableMapping, bool getAutoIncrementValue)
        {
            CommonCommand commonCommand = new CommonCommand();
            // Create Insert command text.
            System.Text.StringBuilder insertCommandTextStringBuilder = new StringBuilder();
#if (PocketPC || Smartphone || WindowsCE)
            insertCommandTextStringBuilder.Append(string.Format("INSERT INTO {0} ", this.dataTableDefinition.SourceName));
#else
            insertCommandTextStringBuilder.AppendFormat("INSERT INTO {0} ", this.dataTableDefinition.SourceName);
#endif
            System.Text.StringBuilder columnStringBuilder = new StringBuilder();
            System.Text.StringBuilder parameterStringBuilder = new StringBuilder();
            string autoIncrementColumn = GetAutoIncrementColumn();
            if (autoIncrementColumn != null)
            {
                System.Text.StringBuilder autoIncrementStringBuilder = new StringBuilder();
                if (useColumnsForParameterNames)
                {
                    foreach (System.Data.Common.DataColumnMapping dataColumnMapping in dataTableMapping.ColumnMappings)
                    {
                        string columnName = dataColumnMapping.DataSetColumn;
                        DataColumnDefinition dataColumnDefinition = this.dataTableDefinition.Columns[columnName];
                        if (dataColumnMapping.DataSetColumn != autoIncrementColumn)
                        {
                            columnStringBuilder.Append(dataColumnDefinition.SourceName);
                            columnStringBuilder.Append(", ");
                            string parameterName = string.Format("@{0}", columnName);
                            parameterStringBuilder.Append(parameterName);
                            
                            commonCommand.Parameters.Add(new CommonParameter(parameterName, dataColumnDefinition.DbType, System.Data.ParameterDirection.Input, dataColumnMapping.DataSetColumn, System.Data.DataRowVersion.Current, false));

                            parameterStringBuilder.Append(", ");
                        }
                        else
                        {
                            if (getAutoIncrementValue)
                            {
                                GenerateAutoIncrementCommand(dataTableMapping, ref autoIncrementStringBuilder, dataColumnMapping, dataColumnDefinition);
                            }
                        }
                    }
                    RemoveEnd(ref columnStringBuilder, 2);
                    RemoveEnd(ref parameterStringBuilder, 2);
                }
                else
                {
                    int parameterNo = 1;
                    foreach (System.Data.Common.DataColumnMapping dataColumnMapping in dataTableMapping.ColumnMappings)
                    {
                        string columnName = dataColumnMapping.DataSetColumn;
                        DataColumnDefinition dataColumnDefinition = this.dataTableDefinition.Columns[columnName];
                        if (dataColumnMapping.DataSetColumn != autoIncrementColumn)
                        {
                            columnStringBuilder.Append(dataColumnDefinition.SourceName);
                            columnStringBuilder.Append(", ");
                            string parameterName = string.Format(PARAMETERNAMEFORMAT, parameterNo);
                            parameterStringBuilder.Append(parameterName);
                            
                            commonCommand.Parameters.Add(new CommonParameter(parameterName, dataColumnDefinition.DbType, System.Data.ParameterDirection.Input, dataColumnMapping.DataSetColumn, System.Data.DataRowVersion.Current, false));

                            parameterNo++;
                            parameterStringBuilder.Append(", ");
                        }
                        else
                        {
                            if (getAutoIncrementValue)
                            {
                                GenerateAutoIncrementCommand(dataTableMapping, ref autoIncrementStringBuilder, dataColumnMapping, dataColumnDefinition);
                            }
                        }
                    }
                    RemoveEnd(ref columnStringBuilder, 2);
                    RemoveEnd(ref parameterStringBuilder, 2);
                }
                if (getAutoIncrementValue)
                {
#if (PocketPC || Smartphone || WindowsCE)
                    insertCommandTextStringBuilder.Append(string.Format("({0}) VALUES ({1}); {2}", columnStringBuilder, parameterStringBuilder, autoIncrementStringBuilder));
#else
                    insertCommandTextStringBuilder.AppendFormat("({0}) VALUES ({1}); {2}", columnStringBuilder, parameterStringBuilder, autoIncrementStringBuilder);
#endif
                }
                else
                {
#if (PocketPC || Smartphone || WindowsCE)
                    insertCommandTextStringBuilder.Append(string.Format("({0}) VALUES ({1})", columnStringBuilder, parameterStringBuilder));
#else
                    insertCommandTextStringBuilder.AppendFormat("({0}) VALUES ({1})", columnStringBuilder, parameterStringBuilder);
#endif
                }
            }
            else
            {
                if (useColumnsForParameterNames)
                {
                    foreach (System.Data.Common.DataColumnMapping dataColumnMapping in dataTableMapping.ColumnMappings)
                    {
                        string columnName = dataColumnMapping.DataSetColumn;
                        DataColumnDefinition dataColumnDefinition = this.dataTableDefinition.Columns[columnName];
                        columnStringBuilder.Append(dataColumnDefinition.SourceName);
                        columnStringBuilder.Append(", ");
                        string parameterName = string.Format("@{0}", columnName);
                        parameterStringBuilder.Append(parameterName);
                                               
                        commonCommand.Parameters.Add(new CommonParameter(parameterName, dataColumnDefinition.DbType, System.Data.ParameterDirection.Input, dataColumnMapping.DataSetColumn, System.Data.DataRowVersion.Current, false));

                        parameterStringBuilder.Append(", ");
                    }
                    RemoveEnd(ref columnStringBuilder, 2);
                    RemoveEnd(ref parameterStringBuilder, 2);
                }
                else
                {
                    int parameterNo = 1;
                    foreach (System.Data.Common.DataColumnMapping dataColumnMapping in dataTableMapping.ColumnMappings)
                    {
                        string columnName = dataColumnMapping.DataSetColumn;
                        DataColumnDefinition dataColumnDefinition = this.dataTableDefinition.Columns[columnName];
                        columnStringBuilder.Append(dataColumnDefinition.SourceName);
                        columnStringBuilder.Append(", ");
                        string parameterName = string.Format(PARAMETERNAMEFORMAT, parameterNo);
                        parameterStringBuilder.Append(parameterName);
                                               
                        commonCommand.Parameters.Add(new CommonParameter(parameterName, dataColumnDefinition.DbType, System.Data.ParameterDirection.Input, dataColumnMapping.DataSetColumn, System.Data.DataRowVersion.Current, false));

                        parameterNo++;
                        parameterStringBuilder.Append(", ");
                    }
                    RemoveEnd(ref columnStringBuilder, 2);
                    RemoveEnd(ref parameterStringBuilder, 2);
                }
#if (PocketPC || Smartphone || WindowsCE)
                insertCommandTextStringBuilder.Append(string.Format("({0}) VALUES ({1})", columnStringBuilder, parameterStringBuilder));
#else
                insertCommandTextStringBuilder.AppendFormat("({0}) VALUES ({1})", columnStringBuilder, parameterStringBuilder);
#endif
            }
            commonCommand.CommandText = insertCommandTextStringBuilder.ToString();
            return commonCommand;
        }
        #endregion
        #region Update 
        /// <summary>
        /// Gets the "Update" command.
        /// </summary>
        /// <param name="dataTableMapping">Data table mapping.</param>
        /// <param name="updateAutoIncrementValue">Update the value of autoincrement.</param>
        /// <param name="parameterNo">parameter no.</param>
        /// <returns>the "Update" command.</returns>
        public CommonCommand GetUpdateCommand(System.Data.Common.DataTableMapping dataTableMapping, bool updateAutoIncrementValue, ref int parameterNo)
        {
            CommonCommand commonCommand = new CommonCommand();
            // Create Update command text.
            System.Text.StringBuilder updateCommandTextStringBuilder = new StringBuilder();
#if (PocketPC || Smartphone || WindowsCE)
            updateCommandTextStringBuilder.Append(string.Format("UPDATE {0} SET ", this.dataTableDefinition.SourceName));
#else
            updateCommandTextStringBuilder.AppendFormat("UPDATE {0} SET ", this.dataTableDefinition.SourceName);
#endif
            string autoIncrementColumn = GetAutoIncrementColumn();
            if ((autoIncrementColumn != null) && (!updateAutoIncrementValue))
            {
                if (useColumnsForParameterNames)
                {
                    foreach (System.Data.Common.DataColumnMapping dataColumnMapping in dataTableMapping.ColumnMappings)
                    {
                        if (dataColumnMapping.DataSetColumn != autoIncrementColumn)
                        {
                            string columnName = dataColumnMapping.DataSetColumn;
                            DataColumnDefinition dataColumnDefinition = this.dataTableDefinition.Columns[columnName];
                            string parameterName = string.Format("@{0}", columnName);
#if (PocketPC || Smartphone || WindowsCE)
                            updateCommandTextStringBuilder.Append(string.Format("{0} = {1}", dataColumnDefinition.SourceName, parameterName));
#else
                            updateCommandTextStringBuilder.AppendFormat("{0} = {1}", dataColumnDefinition.SourceName, parameterName);
#endif
                            
                            commonCommand.Parameters.Add(new CommonParameter(parameterName, dataColumnDefinition.DbType, System.Data.ParameterDirection.Input, dataColumnMapping.DataSetColumn, System.Data.DataRowVersion.Current, false));

                            updateCommandTextStringBuilder.Append(", ");
                        }
                    }
                    RemoveEnd(ref updateCommandTextStringBuilder, 2);
                }
                else
                {
                    foreach (System.Data.Common.DataColumnMapping dataColumnMapping in dataTableMapping.ColumnMappings)
                    {
                        if (dataColumnMapping.DataSetColumn != autoIncrementColumn)
                        {
                            string columnName = dataColumnMapping.DataSetColumn;
                            DataColumnDefinition dataColumnDefinition = this.dataTableDefinition.Columns[columnName];
                            string parameterName = string.Format(PARAMETERNAMEFORMAT, parameterNo);
#if (PocketPC || Smartphone || WindowsCE)
                            updateCommandTextStringBuilder.Append(string.Format("{0} = {1}", dataColumnDefinition.SourceName, parameterName));
#else
                            updateCommandTextStringBuilder.AppendFormat("{0} = {1}", dataColumnDefinition.SourceName, parameterName);
#endif
                            
                            commonCommand.Parameters.Add(new CommonParameter(parameterName, dataColumnDefinition.DbType, System.Data.ParameterDirection.Input, dataColumnMapping.DataSetColumn, System.Data.DataRowVersion.Current, false));

                            parameterNo++;
                            updateCommandTextStringBuilder.Append(", ");
                        }
                    }
                    RemoveEnd(ref updateCommandTextStringBuilder, 2);
                }
            }
            else
            {
                if (useColumnsForParameterNames)
                {
                    foreach (System.Data.Common.DataColumnMapping dataColumnMapping in dataTableMapping.ColumnMappings)
                    {
                        string columnName = dataColumnMapping.DataSetColumn;
                        DataColumnDefinition dataColumnDefinition = this.dataTableDefinition.Columns[columnName];
                        string parameterName = string.Format("@{0}", columnName);
#if (PocketPC || Smartphone || WindowsCE)
                        updateCommandTextStringBuilder.Append(string.Format("{0} = {1}", dataColumnDefinition.SourceName, parameterName));
#else
                        updateCommandTextStringBuilder.AppendFormat("{0} = {1}", dataColumnDefinition.SourceName, parameterName);
#endif
                                               
                        commonCommand.Parameters.Add(new CommonParameter(parameterName, dataColumnDefinition.DbType, System.Data.ParameterDirection.Input, dataColumnMapping.DataSetColumn, System.Data.DataRowVersion.Current, false));

                        updateCommandTextStringBuilder.Append(", ");
                    }
                    RemoveEnd(ref updateCommandTextStringBuilder, 2);
                }
                else
                {
                    foreach (System.Data.Common.DataColumnMapping dataColumnMapping in dataTableMapping.ColumnMappings)
                    {
                        string columnName = dataColumnMapping.DataSetColumn;
                        DataColumnDefinition dataColumnDefinition = this.dataTableDefinition.Columns[columnName];
                        string parameterName = string.Format(PARAMETERNAMEFORMAT, parameterNo);
#if (PocketPC || Smartphone || WindowsCE)
                        updateCommandTextStringBuilder.Append(string.Format("{0} = {1}", dataColumnDefinition.SourceName, parameterName));
#else
                        updateCommandTextStringBuilder.AppendFormat("{0} = {1}", dataColumnDefinition.SourceName, parameterName);
#endif
                       
                        commonCommand.Parameters.Add(new CommonParameter(parameterName, dataColumnDefinition.DbType, System.Data.ParameterDirection.Input, dataColumnMapping.DataSetColumn, System.Data.DataRowVersion.Current, false));

                        parameterNo++;
                        updateCommandTextStringBuilder.Append(", ");
                    }
                    RemoveEnd(ref updateCommandTextStringBuilder, 2);
                }
            }
            commonCommand.CommandText = updateCommandTextStringBuilder.ToString();
            return commonCommand;
        }

        /// <summary>
        /// Gets the "Update" command.
        /// </summary>
        /// <param name="dataTableMapping">Data table mapping.</param>
        /// <param name="updateAutoIncrementValue">Update the value of autoincrement.</param>
        /// <param name="checkConcurrency">Check concurrency.</param>
        /// <returns>the "Update" command.</returns>
        public CommonCommand GetUpdateCommand(System.Data.Common.DataTableMapping dataTableMapping, bool updateAutoIncrementValue, bool checkConcurrency)
        {
            int parameterNo = 1;
            CommonCommand commonCommand = GetUpdateCommand(dataTableMapping, updateAutoIncrementValue, ref parameterNo);
            CommonCommand conditionCommand = BuildConditionCommand(this.DefaultDataTableMapping, checkConcurrency, ref parameterNo);
            return AppendConditionCommand(commonCommand, conditionCommand);
        }

        /// <summary>
        /// Gets the "Update" command.
        /// </summary>
        /// <param name="dataTableMapping">Data table mapping.</param>
        /// <param name="updateAutoIncrementValue">Update the value of autoincrement.</param>
        /// <param name="parameterNo">parameter no.</param>
        /// <param name="conditionCommand">"Condition" Command.</param>
        /// <returns>the "Update" command.</returns>
        public CommonCommand GetUpdateCommand(System.Data.Common.DataTableMapping dataTableMapping, bool updateAutoIncrementValue, ref int parameterNo, CommonCommand conditionCommand)
        {
            CommonCommand commonCommand = GetUpdateCommand(dataTableMapping, updateAutoIncrementValue, ref parameterNo);
            return AppendMappingCommand(commonCommand, conditionCommand);
        }

        /// <summary>
        /// Gets the "Update" command.
        /// </summary>
        /// <param name="dataTableMapping">Data table mapping.</param>
        /// <param name="updateAutoIncrementValue">Update the value of autoincrement.</param>
        /// <param name="parameterNo">parameter no.</param>
        /// <param name="conditionCommand">"Condition" Command.</param>
        /// <returns>the "Update" command.</returns>
        public CommonCommand GetUpdateCommand(System.Data.Common.DataTableMapping dataTableMapping, bool updateAutoIncrementValue, CommonCommand conditionCommand)
        {
            int parameterNo = 1;
            CommonCommand commonCommand = GetUpdateCommand(dataTableMapping, updateAutoIncrementValue, ref parameterNo);
            return AppendMappingCommand(commonCommand, conditionCommand);
        }
        #endregion

        #region Normal Functions
        /// <summary>
        /// The item count in collection.
        /// </summary>
        /// <returns>Common Command.</returns>
        public virtual CommonCommand GetDataCountCommand()
        {
            RaisingStudio.Data.CommonCommand commandCommand = new CommonCommand();
            commandCommand.CommandText = string.Format("SELECT COUNT(*) FROM {0}", this.dataTableDefinition.SourceName);
            return commandCommand;
        }

        /// <summary>
        /// The item count in collection.
        /// </summary>
        /// <param name="conditionCommand">"Condition" Command.</param>
        /// <returns>Common Command.</returns>
        public virtual CommonCommand GetDataCountCommand(CommonCommand conditionCommand)
        {
            RaisingStudio.Data.CommonCommand commonCommand = GetDataCountCommand();
            return AppendMappingCommand(commonCommand, conditionCommand);
        }

        /// <summary>
        /// The sum value.
        /// </summary>
        /// <param name="columnName">Column name.</param>
        /// <returns>Common Command.</returns>
        public virtual CommonCommand GetSumDataCommand(string columnName)
        {
            RaisingStudio.Data.CommonCommand commandCommand = new CommonCommand();
            commandCommand.CommandText = string.Format("SELECT SUM({0}) FROM {1}", this.dataTableDefinition.Columns[columnName].SourceName, this.dataTableDefinition.SourceName);
            return commandCommand;
        }

        /// <summary>
        /// The sum value.
        /// </summary>
        /// <param name="columnName">Column name.</param>
        /// <param name="conditionCommand">"Condition" Command.</param>
        /// <returns>Common Command.</returns>
        public virtual CommonCommand GetSumDataCommand(string columnName, CommonCommand conditionCommand)
        {
            RaisingStudio.Data.CommonCommand commonCommand = GetSumDataCommand(columnName);
            return AppendMappingCommand(commonCommand, conditionCommand);
        }

        /// <summary>
        /// The avg value.
        /// </summary>
        /// <param name="columnName">Column name.</param>
        /// <returns>Common Command.</returns>
        public virtual CommonCommand GetAvgDataCommand(string columnName)
        {
            RaisingStudio.Data.CommonCommand commandCommand = new CommonCommand();
            commandCommand.CommandText = string.Format("SELECT AVG({0}) FROM {1}", this.dataTableDefinition.Columns[columnName].SourceName, this.dataTableDefinition.SourceName);
            return commandCommand;
        }

        /// <summary>
        /// The avg value.
        /// </summary>
        /// <param name="columnName">Column name.</param>
        /// <param name="conditionCommand">"Condition" Command.</param>
        /// <returns>Common Command.</returns>
        public virtual CommonCommand GetAvgDataCommand(string columnName, CommonCommand conditionCommand)
        {
            RaisingStudio.Data.CommonCommand commonCommand = GetAvgDataCommand(columnName);
            return AppendMappingCommand(commonCommand, conditionCommand);
        }

        /// <summary>
        /// The max value.
        /// </summary>
        /// <param name="columnName">Column name.</param>
        /// <returns>Common Command.</returns>
        public virtual CommonCommand GetMaxDataCommand(string columnName)
        {
            RaisingStudio.Data.CommonCommand commandCommand = new CommonCommand();
            commandCommand.CommandText = string.Format("SELECT MAX({0}) FROM {1}", this.dataTableDefinition.Columns[columnName].SourceName, this.dataTableDefinition.SourceName);
            return commandCommand;
        }

        /// <summary>
        /// The max value.
        /// </summary>
        /// <param name="columnName">Column name.</param>
        /// <param name="conditionCommand">"Condition" Command.</param>
        /// <returns>Common Command.</returns>
        public virtual CommonCommand GetMaxDataCommand(string columnName, CommonCommand conditionCommand)
        {
            RaisingStudio.Data.CommonCommand commonCommand = GetMaxDataCommand(columnName);
            return AppendMappingCommand(commonCommand, conditionCommand);
        }

        /// <summary>
        /// The min vlaue.
        /// </summary>
        /// <param name="columnName">Column name.</param>
        /// <returns>Common Command.</returns>
        public virtual CommonCommand GetMinDataCommand(string columnName)
        {
            RaisingStudio.Data.CommonCommand commandCommand = new CommonCommand();
            commandCommand.CommandText = string.Format("SELECT MIN({0}) FROM {1}", this.dataTableDefinition.Columns[columnName].SourceName, this.dataTableDefinition.SourceName);
            return commandCommand;
        }
        
        /// <summary>
        /// The min vlaue.
        /// </summary>
        /// <param name="columnName">Column name.</param>
        /// <param name="conditionCommand">"Condition" Command.</param>
        /// <returns>Common Command.</returns>
        public virtual CommonCommand GetMinDataCommand(string columnName, CommonCommand conditionCommand)
        {
            RaisingStudio.Data.CommonCommand commonCommand = GetMinDataCommand(columnName);
            return AppendMappingCommand(commonCommand, conditionCommand);
        }
        #endregion

        /// <summary>
        /// Gets the "Exists" command.
        /// </summary>
        /// <param name="dataTableMapping">Data table mapping.</param>
        /// <returns>the "Exists" command.</returns>
        public CommonCommand GetExistsCommand(System.Data.Common.DataTableMapping dataTableMapping)
        {
            int parameterNo = 1;
            CommonCommand commonCommand = new CommonCommand();
            System.Text.StringBuilder existsCommandTextStringBuilder = new StringBuilder();
#if (PocketPC || Smartphone || WindowsCE)
            existsCommandTextStringBuilder.Append(string.Format("SELECT COUNT(*) FROM {0} WHERE ", this.dataTableDefinition.SourceName));
#else
            existsCommandTextStringBuilder.AppendFormat("SELECT COUNT(*) FROM {0} WHERE ", this.dataTableDefinition.SourceName);
#endif

            string[] primaryKeyNames = GetPrimaryKeyNames();
            if ((primaryKeyNames != null) && (primaryKeyNames.Length > 0))
            {
                BuildNormalConditionCommand(dataTableMapping, commonCommand, ref existsCommandTextStringBuilder, ref parameterNo);
            }
            else
            {
                // Check concurrency.
                BuildCheckConcurrencyConditionCommand(dataTableMapping, commonCommand, ref existsCommandTextStringBuilder, ref parameterNo);
            }

            commonCommand.CommandText = existsCommandTextStringBuilder.ToString();
            return commonCommand;
        }

        /// <summary>
        /// Gets the "Select" command include condition.
        /// </summary>
        /// <param name="dataTableMapping">Data table mapping.</param>
        /// <returns>"Select"  Command.</returns>
        public CommonCommand GetConditionSelectCommand(out System.Data.Common.DataTableMapping dataTableMapping)
        {
            int parameterNo = 1;
            CommonCommand commonCommand = GetSelectCommand(out dataTableMapping);
            CommonCommand conditionCommand = BuildConditionCommand(this.DefaultDataTableMapping, false, ref parameterNo);
            return AppendConditionCommand(commonCommand, conditionCommand);
        }

        /// <summary>
        /// Gets the "Select" command include condition.
        /// </summary>
        /// <param name="columnNames">An array of Columns.</param>
        /// <param name="dataTableMapping">Data table mapping.</param>
        /// <returns>"Select"  Command.</returns>
        public CommonCommand GetConditionSelectCommand(string[] columnNames, out System.Data.Common.DataTableMapping dataTableMapping)
        {
            int parameterNo = 1;
            CommonCommand commonCommand = GetSelectCommand(columnNames, out dataTableMapping);
            CommonCommand conditionCommand = BuildConditionCommand(this.DefaultDataTableMapping, false, ref parameterNo);
            return AppendConditionCommand(commonCommand, conditionCommand);
        }

        /// <summary>
        /// Create Condition Command.
        /// </summary>
        /// <param name="dataTableMapping">mapping.</param>
        /// <returns>Condition Command.</returns>
        public virtual CommonCommand GetNormalConditionCommand(System.Data.Common.DataTableMapping dataTableMapping)
        {
            int parameterNo = 1;
            CommonCommand commonCommand = new CommonCommand();
            System.Text.StringBuilder conditionStringBuilder = new StringBuilder();
            BuildNormalConditionCommand(dataTableMapping, commonCommand, ref conditionStringBuilder, ref parameterNo);
            commonCommand.CommandText = conditionStringBuilder.ToString();
            return commonCommand;
        }

        #region View Mode
        /// <summary>
        /// Create FROM sub command.
        /// </summary>
        /// <param name="xmlElement">xml element.</param>
        /// <param name="dataTableMapping">mapping.</param>
        /// <returns>FROM sub command.</returns>
        private static string BuildViewFromCommandText(System.Xml.XmlElement xmlElement, out System.Data.Common.DataTableMapping dataTableMapping)
        {
            string fromCommandText;

            string dataTableTableName = xmlElement.Attributes["TableName"].Value.Trim();
            string dataTableSourceName = dataTableTableName;
            if (xmlElement.Attributes["SourceName"] != null)
            {
                dataTableSourceName = xmlElement.Attributes["SourceName"].Value.Trim();
            }
            string dataViewSourceName = dataTableSourceName;
            if (xmlElement.Attributes["ViewName"] != null)
            {
                dataViewSourceName = xmlElement.Attributes["ViewName"].Value.Trim();
            }
            string dataTableSourceAlias = dataTableSourceName;
            if (xmlElement.Attributes["SourceAlias"] != null)
            {
                dataTableSourceAlias = xmlElement.Attributes["SourceAlias"].Value.Trim();
            }


            if (dataViewSourceName == dataTableSourceAlias)
            {
                fromCommandText = string.Format(" FROM {0}", dataViewSourceName);
                dataTableMapping = new System.Data.Common.DataTableMapping(dataTableSourceName, dataTableTableName);
            }
            else
            {
                fromCommandText = string.Format(" FROM {0} {1}", dataViewSourceName, dataTableSourceAlias);
                dataTableMapping = new System.Data.Common.DataTableMapping(dataTableSourceAlias, dataTableTableName);
            }

            return fromCommandText;
        }

        /// <summary>
        /// Create select command text.
        /// </summary>
        /// <param name="dataTableMapping">mapping.</param>
        /// <returns>select command text.</returns>
        public virtual string BuildViewSelectCommandText(out System.Data.Common.DataTableMapping dataTableMapping)
        {
            string fromCommandText = BuildViewFromCommandText(this.xmlElement, out dataTableMapping);

            System.Text.StringBuilder selectCommandTextStringBuilder = new StringBuilder();
            selectCommandTextStringBuilder.Append("SELECT ");
            foreach (System.Xml.XmlNode childXmlNode in xmlElement)
            {
                if (childXmlNode is System.Xml.XmlElement)
                {
                    System.Xml.XmlElement childXmlElement = childXmlNode as System.Xml.XmlElement;
                    if (childXmlElement.Name == "DataColumn")
                    {
                        string dataColumnColumnName = childXmlElement.Attributes["ColumnName"].Value.Trim();

                        AppendColumnCommandText(dataColumnColumnName, childXmlElement, ref selectCommandTextStringBuilder, ref dataTableMapping);
                        // Add comma as a splitter.
                        selectCommandTextStringBuilder.Append(", ");
                    }
                }
            }
            // remove the comma added last.
            RemoveEnd(ref selectCommandTextStringBuilder, 2);

            // Add select table command text.
            selectCommandTextStringBuilder.Append(fromCommandText);
            // Add addtion Command.
            AppendAddtionCommandText(this.xmlElement, ref selectCommandTextStringBuilder);

            return selectCommandTextStringBuilder.ToString();
        }

        /// <summary>
        /// Create select command text.
        /// </summary>
        /// <param name="columnNames">An array of Columns.</param>
        /// <param name="dataTableMapping">mapping.</param>
        /// <returns>select command text.</returns>
        public virtual string BuildViewSelectCommandText(string[] columnNames, out System.Data.Common.DataTableMapping dataTableMapping)
        {
            string fromCommandText = BuildViewFromCommandText(this.xmlElement, out dataTableMapping);

            System.Text.StringBuilder selectCommandTextStringBuilder = new StringBuilder();
            selectCommandTextStringBuilder.Append("SELECT ");
            foreach (System.Xml.XmlNode childXmlNode in xmlElement)
            {
                if (childXmlNode is System.Xml.XmlElement)
                {
                    System.Xml.XmlElement childXmlElement = childXmlNode as System.Xml.XmlElement;
                    if (childXmlElement.Name == "DataColumn")
                    {
                        string dataColumnColumnName = childXmlElement.Attributes["ColumnName"].Value.Trim();

                        if (ContainsValue(columnNames, dataColumnColumnName))
                        {
                            AppendColumnCommandText(dataColumnColumnName, childXmlElement, ref selectCommandTextStringBuilder, ref dataTableMapping);
                            // Add comma as a splitter.
                            selectCommandTextStringBuilder.Append(", ");
                        }
                    }
                }
            }
            // remove the comma added last.
            RemoveEnd(ref selectCommandTextStringBuilder, 2);

            // Add select table command text.
            selectCommandTextStringBuilder.Append(fromCommandText);
            // Add addtion Command.
            AppendAddtionCommandText(this.xmlElement, ref selectCommandTextStringBuilder);

            return selectCommandTextStringBuilder.ToString();
        }

        /// <summary>
        /// addtion "Condition" Command.
        /// </summary>
        /// <remarks>
        /// pay attention to the parameter name.
        /// </remarks>
        /// <param name="commonCommand">Common Command.</param>
        /// <param name="conditionCommand">Condition Command.</param>
        /// <returns>addtion Condition Command Common Command.</returns>
        public virtual CommonCommand AppendViewMappingCommand(CommonCommand commonCommand, CommonCommand conditionCommand)
        {
            string conditionCommandText = conditionCommand.CommandText;
            conditionCommandText = AdvancedReplaceViewMappingCommandText(conditionCommandText);
            commonCommand.CommandText = string.Format("{0}{1}{2}", commonCommand.CommandText, CONDITIONCOMMANDAPPENDTEXT, conditionCommandText);
            // addtion parameters.
            foreach (CommonParameter commonParameter in conditionCommand.Parameters)
            {
                commonCommand.Parameters.Add(commonParameter);
            }
            return commonCommand;
        }

        private string AdvancedReplaceViewMappingCommandText(string conditionCommandText)
        {
            if ((ParseExpression) && (StringExtension.Contains(conditionCommandText, "'")))
            {
                // column name in string.
                Parser parser = new Parser(conditionCommandText);
                parser.ParseLevel = ParseLevel.Character;
                List<Expression> expressions = parser.Parse();
                if ((expressions != null) && (expressions.Count > 0))
                {
                    StringBuilder stringBuilder = new StringBuilder();
                    int current = 0;
                    foreach (Expression expression in expressions)
                    {
                        if (expression.Begin.Position > current)
                        {
                            string commandExpression = conditionCommandText.Substring(current, expression.Begin.Position - current);
                            stringBuilder.Append(ReplaceViewMappingCommandText(commandExpression));
                            stringBuilder.Append(conditionCommandText.Substring(expression.Begin.Position, expression.End.Position - expression.Begin.Position));
                            current = expression.End.Position;
                        }
                        else
                        {
                            stringBuilder.Append(conditionCommandText.Substring(expression.Begin.Position, expression.End.Position - expression.Begin.Position));
                            current = expression.End.Position;
                        }
                    }
                    if (current < conditionCommandText.Length)
                    {
                        string commandExpression = conditionCommandText.Substring(current, conditionCommandText.Length - current);
                        stringBuilder.Append(ReplaceViewMappingCommandText(commandExpression));
                    }
                    conditionCommandText = stringBuilder.ToString();
                }
                else
                {
                    conditionCommandText = ReplaceViewMappingCommandText(conditionCommandText);
                }
            }
            else
            {
                conditionCommandText = ReplaceViewMappingCommandText(conditionCommandText);
            }
            return conditionCommandText;
        }

        private string ReplaceViewMappingCommandText(string conditionCommandText)
        {
            string tableName = string.Format(".[{0}]", this.dataTableDefinition.TableName);
            string sourceName = this.DataViewDefinition.SourceName;
            conditionCommandText = conditionCommandText.Replace(tableName, sourceName);
            foreach (string columnName in this.dataTableDefinition.Columns.Keys)
            {
                string columnColumnName = string.Format("[{0}]", columnName);
                string columnSourceName = this.dataTableDefinition.Columns[columnName].SourceName;
                conditionCommandText = conditionCommandText.Replace(columnColumnName, columnSourceName);
            }
            foreach (string columnName in this.DataViewDefinition.Columns.Keys)
            {
                string columnColumnName = string.Format("[{0}]", columnName);
                string columnSourceName = this.DataViewDefinition.Columns[columnName].SourceName;
                conditionCommandText = conditionCommandText.Replace(columnColumnName, columnSourceName);
            }
            return conditionCommandText;
        }

        /// <summary>
        /// Gets mapping ReplacedCommon Command.
        /// </summary>
        /// <param name="commonCommand">Common Command.</param>
        /// <returns>mapping ReplacedCommon Command.</returns>
        public virtual CommonCommand GetViewMappingCommand(CommonCommand commonCommand)
        {
            string commandText = commonCommand.CommandText;
            commandText = AdvancedReplaceViewMappingCommandText(commandText);
            CommonCommand mappingCommand = new CommonCommand();
            mappingCommand.CommandText = commandText;
            // addtion parameters.
            foreach (CommonParameter commonParameter in commonCommand.Parameters)
            {
                mappingCommand.Parameters.Add(commonParameter);
            }
            return mappingCommand;
        }
    
        /// <summary>
        /// Gets "Select"  Command.
        /// </summary>
        /// <param name="conditionCommand">"Condition" Command.</param>
        /// <param name="dataTableMapping">Data table mapping.</param>
        /// <returns>"Select"  Command.</returns>
        public CommonCommand GetViewSelectCommand(CommonCommand conditionCommand, out System.Data.Common.DataTableMapping dataTableMapping)
        {
            CommonCommand commonCommand = new CommonCommand();
            string commandText = BuildViewSelectCommandText(out dataTableMapping);
            commonCommand.CommandText = commandText;
            return AppendViewMappingCommand(commonCommand, conditionCommand);
        }

        /// <summary>
        /// Gets "Select"  Command.
        /// </summary>
        /// <param name="columnNames">An array of Columns.</param>
        /// <param name="conditionCommand">"Condition" Command.</param>
        /// <param name="dataTableMapping">Data table mapping.</param>
        /// <returns>"Select"  Command.</returns>
        public CommonCommand GetViewSelectCommand(string[] columnNames, CommonCommand conditionCommand, out System.Data.Common.DataTableMapping dataTableMapping)
        {
            CommonCommand commonCommand = new CommonCommand();
            string commandText = BuildViewSelectCommandText(columnNames, out dataTableMapping);
            commonCommand.CommandText = commandText;
            return AppendViewMappingCommand(commonCommand, conditionCommand);
        }

        #region Normal Functions
        /// <summary>
        /// The item count in collection.
        /// </summary>
        /// <returns>Common Command.</returns>
        public virtual CommonCommand GetViewDataCountCommand()
        {
            RaisingStudio.Data.CommonCommand commandCommand = new CommonCommand();
            commandCommand.CommandText = string.Format("SELECT COUNT(*) FROM {0}", this.DataViewDefinition.SourceName);
            return commandCommand;
        }

        /// <summary>
        /// The item count in collection.
        /// </summary>
        /// <param name="conditionCommand">"Condition" Command.</param>
        /// <returns>Common Command.</returns>
        public virtual CommonCommand GetViewDataCountCommand(CommonCommand conditionCommand)
        {
            RaisingStudio.Data.CommonCommand commonCommand = GetViewDataCountCommand();
            return AppendViewMappingCommand(commonCommand, conditionCommand);
        }

        /// <summary>
        /// The sum value.
        /// </summary>
        /// <param name="columnName">Column name.</param>
        /// <returns>Common Command.</returns>
        public virtual CommonCommand GetViewSumDataCommand(string columnName)
        {
            RaisingStudio.Data.CommonCommand commandCommand = new CommonCommand();
            if (this.dataTableDefinition.Columns.ContainsKey(columnName))
            {
                commandCommand.CommandText = string.Format("SELECT SUM({0}) FROM {1}", this.dataTableDefinition.Columns[columnName].SourceName, this.DataViewDefinition.SourceName);
            }
            else
            {
                commandCommand.CommandText = string.Format("SELECT SUM({0}) FROM {1}", this.DataViewDefinition.Columns[columnName].SourceName, this.DataViewDefinition.SourceName);
            }
            return commandCommand;
        }

        /// <summary>
        /// The sum value.
        /// </summary>
        /// <param name="columnName">Column name.</param>
        /// <param name="conditionCommand">"Condition" Command.</param>
        /// <returns>Common Command.</returns>
        public virtual CommonCommand GetViewSumDataCommand(string columnName, CommonCommand conditionCommand)
        {
            RaisingStudio.Data.CommonCommand commonCommand = GetViewSumDataCommand(columnName);
            return AppendViewMappingCommand(commonCommand, conditionCommand);
        }

        /// <summary>
        /// The avg value.
        /// </summary>
        /// <param name="columnName">Column name.</param>
        /// <returns>Common Command.</returns>
        public virtual CommonCommand GetViewAvgDataCommand(string columnName)
        {
            RaisingStudio.Data.CommonCommand commandCommand = new CommonCommand();
            if (this.dataTableDefinition.Columns.ContainsKey(columnName))
            {
                commandCommand.CommandText = string.Format("SELECT AVG({0}) FROM {1}", this.dataTableDefinition.Columns[columnName].SourceName, this.DataViewDefinition.SourceName);
            }
            else
            {
                commandCommand.CommandText = string.Format("SELECT AVG({0}) FROM {1}", this.DataViewDefinition.Columns[columnName].SourceName, this.DataViewDefinition.SourceName);
            }
            return commandCommand;
        }

        /// <summary>
        /// The avg value.
        /// </summary>
        /// <param name="columnName">Column name.</param>
        /// <param name="conditionCommand">"Condition" Command.</param>
        /// <returns>Common Command.</returns>
        public virtual CommonCommand GetViewAvgDataCommand(string columnName, CommonCommand conditionCommand)
        {
            RaisingStudio.Data.CommonCommand commonCommand = GetViewAvgDataCommand(columnName);
            return AppendViewMappingCommand(commonCommand, conditionCommand);
        }

        /// <summary>
        /// The max value.
        /// </summary>
        /// <param name="columnName">Column name.</param>
        /// <returns>Common Command.</returns>
        public virtual CommonCommand GetViewMaxDataCommand(string columnName)
        {
            RaisingStudio.Data.CommonCommand commandCommand = new CommonCommand();
            if (this.dataTableDefinition.Columns.ContainsKey(columnName))
            {
                commandCommand.CommandText = string.Format("SELECT MAX({0}) FROM {1}", this.dataTableDefinition.Columns[columnName].SourceName, this.DataViewDefinition.SourceName);
            }
            else
            {
                commandCommand.CommandText = string.Format("SELECT MAX({0}) FROM {1}", this.DataViewDefinition.Columns[columnName].SourceName, this.DataViewDefinition.SourceName);
            }
            return commandCommand;
        }

        /// <summary>
        /// The max value.
        /// </summary>
        /// <param name="columnName">Column name.</param>
        /// <param name="conditionCommand">"Condition" Command.</param>
        /// <returns>Common Command.</returns>
        public virtual CommonCommand GetViewMaxDataCommand(string columnName, CommonCommand conditionCommand)
        {
            RaisingStudio.Data.CommonCommand commonCommand = GetViewMaxDataCommand(columnName);
            return AppendViewMappingCommand(commonCommand, conditionCommand);
        }

        /// <summary>
        /// The min vlaue.
        /// </summary>
        /// <param name="columnName">Column name.</param>
        /// <returns>Common Command.</returns>
        public virtual CommonCommand GetViewMinDataCommand(string columnName)
        {
            RaisingStudio.Data.CommonCommand commandCommand = new CommonCommand();
            if (this.dataTableDefinition.Columns.ContainsKey(columnName))
            {
                commandCommand.CommandText = string.Format("SELECT MIN({0}) FROM {1}", this.dataTableDefinition.Columns[columnName].SourceName, this.DataViewDefinition.SourceName);
            }
            else
            {
                commandCommand.CommandText = string.Format("SELECT MIN({0}) FROM {1}", this.DataViewDefinition.Columns[columnName].SourceName, this.DataViewDefinition.SourceName);
            }
            return commandCommand;
        }

        /// <summary>
        /// The min vlaue.
        /// </summary>
        /// <param name="columnName">Column name.</param>
        /// <param name="conditionCommand">"Condition" Command.</param>
        /// <returns>Common Command.</returns>
        public virtual CommonCommand GetViewMinDataCommand(string columnName, CommonCommand conditionCommand)
        {
            RaisingStudio.Data.CommonCommand commonCommand = GetViewMinDataCommand(columnName);
            return AppendViewMappingCommand(commonCommand, conditionCommand);
        }
        #endregion
        #endregion

        #region include Relation 
        /// <summary>
        /// has relation definition.
        /// </summary>
        /// <param name="xmlElement">xml element.</param>
        /// <returns>has relation definition.</returns>
        public static bool HasIncludeDefinition(System.Xml.XmlElement xmlElement)
        {
            if (xmlElement != null)
            {
                if (xmlElement.Attributes["IncludeDefinition"] != null)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// has source name definition.
        /// </summary>
        /// <param name="xmlElement">xml element.</param>
        /// <returns>has source name definition.</returns>
        public static bool HasSourceName(System.Xml.XmlElement xmlElement)
        {
            if (xmlElement.Attributes["SourceName"] != null)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// Gets include relation definition xml element.
        /// </summary>
        /// <param name="type">Type.</param>
        /// <param name="xmlElement">xml element.</param>
        /// <returns>include relation definition xml element.</returns>
        public static System.Xml.XmlElement GetIncludeXmlElement(System.Type type, System.Xml.XmlElement xmlElement)
        {
            // Has include relation.
            string includeDefinitionName = xmlElement.Attributes["IncludeDefinition"].Value.Trim();

            if (xmlElement.Attributes["IncludeType"] != null)
            {
                string includeTypeName = xmlElement.Attributes["IncludeType"].Value.Trim();
                string typeName = includeTypeName;
                System.Reflection.Assembly assembly;
                if (typeName.IndexOf(",") >= 0)
                {
                    string[] names = typeName.Split(',');
                    assembly = System.Reflection.Assembly.LoadFrom(names[0]);
                    typeName = names[1];
                }
                else
                {
                    assembly = type.Assembly;
                }
                System.Type includeType = assembly.GetType(typeName);

                System.Xml.XmlElement includeXmlElement = DefinitionManager.Instance.LoadXmlDocument(includeType, includeDefinitionName);
                return includeXmlElement;
            }
            else
            {
                System.Xml.XmlElement includeXmlElement = DefinitionManager.Instance.LoadXmlDocument(type, includeDefinitionName);
                return includeXmlElement;
            }
        }
        
        /// <summary>
        /// Create include Relation FROM sub command.
        /// </summary>
        /// <param name="xmlElement">xml element.</param>
        /// <param name="dataTableMapping">mapping.</param>
        /// <returns>FROM sub command.</returns>
        private static string BuildIncludeDefinitionFromCommandText(System.Xml.XmlElement xmlElement, out System.Data.Common.DataTableMapping dataTableMapping)
        {
            string fromCommandText;

            string dataTableTableName = xmlElement.Attributes["TableName"].Value.Trim();
            string dataTableSourceName = dataTableTableName;
            if (xmlElement.Attributes["SourceName"] != null)
            {
                dataTableSourceName = xmlElement.Attributes["SourceName"].Value.Trim();
            }
            string dataTableSourceAlias = dataTableSourceName;
            if (xmlElement.Attributes["SourceAlias"] != null)
            {
                dataTableSourceAlias = xmlElement.Attributes["SourceAlias"].Value.Trim();
            }


            if (dataTableSourceName == dataTableSourceAlias)
            {
                fromCommandText = string.Format(" FROM {0}", dataTableSourceName);
                dataTableMapping = new System.Data.Common.DataTableMapping(dataTableSourceName, dataTableTableName);
            }
            else
            {
                fromCommandText = string.Format(" FROM {0} {1}", dataTableSourceName, dataTableSourceAlias);
                dataTableMapping = new System.Data.Common.DataTableMapping(dataTableSourceAlias, dataTableTableName);
            }

            return fromCommandText;
        }

        /// <summary>
        /// Create include Relation "Select"  command text.
        /// </summary>
        /// <param name="dataTableMapping">mapping.</param>
        /// <returns>"Select"  command text.</returns>
        public virtual string BuildIncludeDefinitionSelectCommandText(out System.Data.Common.DataTableMapping dataTableMapping)
        {
            string fromCommandText = BuildIncludeDefinitionFromCommandText(this.xmlElement, out dataTableMapping);

            System.Text.StringBuilder selectCommandTextStringBuilder = new StringBuilder();
            selectCommandTextStringBuilder.Append("SELECT ");
            foreach (System.Xml.XmlNode childXmlNode in xmlElement)
            {
                if (childXmlNode is System.Xml.XmlElement)
                {
                    System.Xml.XmlElement childXmlElement = childXmlNode as System.Xml.XmlElement;
                    if (childXmlElement.Name == "DataColumn")
                    {
                        string dataColumnColumnName = childXmlElement.Attributes["ColumnName"].Value.Trim();

                        AppendColumnCommandText(dataColumnColumnName, childXmlElement, ref selectCommandTextStringBuilder, ref dataTableMapping);
                        // Add comma as a splitter.
                        selectCommandTextStringBuilder.Append(", ");
                    }
                }
            }

            // include Relation 。
            System.Xml.XmlElement includeXmlElement = GetIncludeXmlElement(this.definitionType, xmlElement);
            if (HasSourceName(xmlElement))
            {
                // TODO: single table include.
            }
            else
            {
                // single table include.
                foreach (System.Xml.XmlNode childXmlNode in includeXmlElement)
                {
                    if (childXmlNode is System.Xml.XmlElement)
                    {
                        System.Xml.XmlElement childXmlElement = childXmlNode as System.Xml.XmlElement;
                        if (childXmlElement.Name == "DataColumn")
                        {
                            string dataColumnColumnName = childXmlElement.Attributes["ColumnName"].Value.Trim();

                            AppendColumnCommandText(dataColumnColumnName, childXmlElement, ref selectCommandTextStringBuilder, ref dataTableMapping);
                            // Add comma as a splitter.
                            selectCommandTextStringBuilder.Append(", ");
                        }
                    }
                }
            }

            // remove the comma added last.
            RemoveEnd(ref selectCommandTextStringBuilder, 2);

            // Add select table command text.
            selectCommandTextStringBuilder.Append(fromCommandText);
            // Add addtion Command.
            AppendAddtionCommandText(this.xmlElement, ref selectCommandTextStringBuilder);

            return selectCommandTextStringBuilder.ToString();
        }

        /// <summary>
        /// Create include Relation "Select"  command text.
        /// </summary>
        /// <param name="columnNames">An array of Columns.</param>
        /// <param name="dataTableMapping">mapping.</param>
        /// <returns>"Select"  command text.</returns>
        public virtual string BuildIncludeDefinitionSelectCommandText(string[] columnNames, out System.Data.Common.DataTableMapping dataTableMapping)
        {
            // TODO:
            throw new Exception("The method or operation is not implemented.");
        }
        #endregion
        #endregion
    }
}
